#  Other Applications & Softwares  > Access Tables & Databases >  >  Query Join / Union

## ciprian

So I have these 2 tables:


Table1
ID 
ContractID (Primary)
Value
FinishDate

Table2
ID(Primary)
ContractID (allow duplicate)
AditionalID
Value
FinishDate

The Query should only show the data form the 2 tables if it's in the date range searched.





```
Please Login or Register  to view this content.
```


How do i change this so that it also takes into account the Table2.FinishDate but at the same time keep Table1.FinishDate, since i have the situation when Table1 data is not the requested date range but Table2 is.

Thanks for the help

----------


## Kyle123

Hi

The easiest thing would be to have 2 queries.

Since you need to know which one to call, usually you'd use a stored procedure; however Access doesn't directly support these in the UI. They can be used within the VBA environment however, but it depends how comfortable you are in using code.

----------


## ciprian

i thought that would be the case, i have some exp with vba in excel but in access none unfortunately. 

but wouldn't a FULL JOIN help me in this case ? even though access doesn't support it, it would help to know

----------


## Kyle123

Wouldn't you need some way of telling Access whether to look at the Table2.FinishDate or Table1.FinishDate? Unless you wanted the same end date in both in which case you could use:




```
Please Login or Register  to view this content.
```

----------


## ciprian

the  searched  FinishDate is the same for both Tables but i have cases when data in Table1 is not valid but the linked data in Table2 is valid. 

If i use AND i won't get any results since the data in Table2 will never be the same FininshDate as in Table1.

If i use OR all the data in Table2 is showed in the results of the query

----------


## Kyle123

In that case you'd need some way of telling Access which Query to run - Stored Procedures

So you'd need to say run:



```
Please Login or Register  to view this content.
```


or:



```
Please Login or Register  to view this content.
```


Unless I've misunderstood?

Access can't do this natively so you'd need either 2 queries or use VBA to select which one to run

----------


## Kyle123

Actually, try the following, this might work depending on how you want the data displaying:




```
Please Login or Register  to view this content.
```

----------


## ciprian

I'll try a different approach

Q1




```
Please Login or Register  to view this content.
```


Q2




```
Please Login or Register  to view this content.
```


Separetly these 2 queries give me what i need, how can i merge the results ?

----------


## Kyle123

Try:



```
Please Login or Register  to view this content.
```

----------


## Kyle123

Think I may have misunderstood, see my undeleted post, think this should work?

----------


## ciprian

I made an example, the result in the red balloon should not be there

----------


## Kyle123

Is that the result of this?




```
Please Login or Register  to view this content.
```

----------


## ciprian

nope, i changed the jpg to show the query from where i get the results 

the union thing does not work for me in this case

----------


## Kyle123

You only have criteria on Table1, not Table2 so Access is returning all Table 2 and Table1 where it meets your criteria.

What doesn't work with the SQL UNION query?

----------


## ciprian

same problem with union one

edit: hmm after i closed the db and opened it again it seems to be working, thanks for the help

----------


## Kyle123

Any chance you could upload a sample db?

----------


## ciprian

the forum doesn't allow .db uploads unfortunately, but it seems i managed to make it work using your union example. I'm not really sure why it didn't work the first time i tried it, now it works fine and with some conditional formatting when creating the report i cand get the expected result

thanks a lot for the help

----------


## Kyle123

Glad it's working, you can zip and upload  :Wink:

----------


## ciprian

Morning guys, although I said the issue was resolved, after some more testing I realized it's not quite there yet.

So I made a .zip file with a test db and a .jpg showing a the result how it's now and what I need to achieve. Thanks for the help

----------


## Kyle123

Any chance you could convert this to be compatible with 2003?

----------


## ciprian

here you go

----------


## Kyle123

Sorry, I can't come up with a simple way of doing this without 2 queries.

I'm not even sure that it's possible with standard Access SQL (happy to be proved wrong however!!)

I think you should look at 2 queries then looking at how to choose between them

----------


## ciprian

Thanks for trying, i wasn't looking for a specific way to resolve the issue. If it takes 3 queries to do it, it's fine with me  :Smilie:

----------


## davegugg

How's this:




```
Please Login or Register  to view this content.
```


It seemed to work on your sample database.

----------


## MarvinP

I thought you just have to uncheck the "SHOW" boxes for all of table 1 and this would solve your problem?

Also in your Between(  )  -  I thought the smaller number needed to come first but what do I know?

----------


## Kyle123

Thanks dave, came so close to that it's untrue lol, couldnt quite grt my head round it!

----------


## davegugg

Marvin: All the documentation I can find agrees that the first 'argument' in the between predicate must be greater than the last.  Not sure why it works in the example database the opposite way, but it does.

As far as showing the results from Table 1, the original post wanted most fields from both tables; I don't see how unchecking those would give the requested results.

----------

