Query Based on Two Tables ?

[danbl]

I have two tables with two fields in common; DOB (date of birth) and DOS (Date of Service).

I need a query that gets information from each table based on a date range, (start date and finnish date) when these two values equal each other. I have tried a number of things but have had no luck.

Can someone help me??

 

[vbaInet]

See if this helps:

http://baldyweb.com/OverLap.htm

 

[danbl]

I am not versed in SQL but it appears this might help. How would this be done using expression builder?
To try and clarify what I am trying to do based on a date range, I want values form table 2 based on the DOB and DOS being equal in table 1 and table 2.

Thanks
Dan

 

[vbaInet]

Create a query that will pull together records from both tables including those two fields as well as others.

Since you say you want to return records where these two dates are equal you, set [DOB] = [DOS] in the criteria and change the JOIN to a Right Outter Join where all records from table 1 are included.

 

[danbl]

Ok .... this is over my head (Joins) ... DOB has to equal DOB and DOS has to equal DOS to pull over the other fields I want in the query.

 

[vbaInet]

Ignore the joins for now but you will need it later on.

Under the criteria for DOB you would put [DOS] and under the OR Criteria for DOS you put [DOB] or whatever you've called the field names.

 

[danbl]

Well even a blind pig finds and ear of corn once in awhile!

I removed the DOS relationship and left the relatiionship to DOB and it worked. I assume this is because the between start and end date criteria eliminated the need for that realtionship?

I appreciate your help and I am sure I will be back for more as I learn about Access 03.

Thanks
Dan

 

[vbaInet]

You would have been able to get away with the join. Can you post your db?

 

[danbl]

I don't believe I can due to privacy issues here. I do have another question??

I have an expression in the query that is: (done in expression builder)
Total Time:[end time]-[start time]
How can I format this to be in hours and minutes?

 

[vbaInet]

There's a post on this forum regarding this. Use the search facility and as one of your criteria put BrianWarnock in the username box. He's one of the forum's VIPs that followed up that thread.

 

[danbl]

found the answer .... thanks again!!!!

 

[dkinley]

Here is a good resource. Skip down to the 'Time, adding and subtracting ...'

-dK

 

[vbaInet]

Goodie!! Glad that's sorted.

dk: That's a good link for the OP.

 

[dkinley]

I think I stole it off of RuralGuy a long time ago and put it in my list of favorites.

Funny though, sometimes when I am struggling with dates and times I have found myself googling up brian's name since his understanding is much envied.

-dK

 

[vbaInet]

I think I stole it off of RuralGuy a long time ago and put it in my list of favorites.

Funny though, sometimes when I am struggling with dates and times I have found myself googling up brian's name since his understanding is much envied.

-dK

There's bountiful resources on that site and I sometimes refer posters to the related links.

Lol, Brian does like his queries and dates