Query trouble, Redundant data with table joins and date selects

[wackerpf]

(Access 2007)

I have three tables in a database: tblClients, tblOfficeVisits and tblData

tblClients has a primary key CaseID. This is joined (inner, 1-Many) to CaseID in tblOfficeVisits which contains only three fields (CaseID, VisitNumber and VisitDate). The purpose is to list every time a client comes into the office (roughly every two weeks, but not constant) and record what number visit it was (*Side Question: should I drop the VisitNumber field and calculate it instead?).

tblClients.CaseID is also joined (inner, 1-Many) to CaseID in tblData which also contains only three fields (CaseID, DateTime and DataValue). The purpose of tblData is to list numeric data values that have already been recorded, each from one client at a particular moment in time (ie. a client with CaseID = 20 could have a DataValue of 150 at exactly #20-Jul-09 18:04#).

The goal of my query is to be able to select a particular client CaseID and be able to list all of the data values (tblData.DataValue) that fell between two dates... specifically, dates that are listed on tblOfficeVisits that correspond to that client.
(ie. a client came in for his/her third visit on Jan 10, 2009 and fourth visit on Jan 26, 2009. These two dates are listed in tblOfficeVisits, numbered as such in the VisitNumber field. I need a query to return every data record on tblData that has a DateTime stamp between those two office visit dates)

ANY help is greatly appreciated. Let me know if you need any more information. I am new to complex queries and when I try this the closest I get will give me redundant records in the Recordset... if I SHOULD get 100 data records and the client has had 5 office visits, I end up with 500 records.

Thanks for any help.

 

[jackd]

The dates were just examples... I would need it to pull all of them. I simply picked two random dates to explain how I needed the previous seven days. In reality it would be a list of dates like:<table><tr><td><u>CaseID</u></td><td><u>VisitDate</u></td></tr><tr><td>34</td><td>14 Feb 09</td></tr><tr><td>34</td><td>27 Feb 09</td></tr><tr><td>34</td><td>14 Mar 09</td></tr><tr><td>34</td><td>29 Mar 09</td></tr><tr><td>34</td><td>12 Apr 09</td></tr><tr><td>34</td><td>26 Apr 09</td></tr></table>...and will have records for other clients as well (the number of visits per client will vary).

I need to pull from tblData all the records with a DateTime stamp that fall within the seven days prior to any of the dates in tblOfficeVisits (example shown above).

Did you try my sample code?

 

[wackerpf]

Hey jackd... I did. Looks to be working =)

<code>(DateDiff("d",[DateTime],[VisitDate]))<=7 And (DateDiff("d",[DateTime],[VisitDate]))>=0</code>

This DateDiff function seems to be key here. I really appreciate your help with this. Any recommendation on how I could vary the time difference to values other than whole day increments? (Say I wanted to run the same query, but I wanted to include 7 days 1 hour and 30 minutes instead... any suggestion beyond simply converting this to a decimal?)

Thanks again, the help was great.

 

[jackd]

Hey jackd... I did. Looks to be working =)

<code>(DateDiff("d",[DateTime],[VisitDate]))<=7 And (DateDiff("d",[DateTime],[VisitDate]))>=0</code>

This DateDiff function seems to be key here. I really appreciate your help with this. Any recommendation on how I could vary the time difference to values other than whole day increments? (Say I wanted to run the same query, but I wanted to include 7 days 1 hour and 30 minutes instead... any suggestion beyond simply converting this to a decimal?)

Thanks again, the help was great.

I looked it up on Google and found this link.
http://allenbrowne.com/casu-13.html

Allen Browne is an excellent resource and his tutorials and tools at top notch.

Good luck.

 

[wackerpf]

I looked it up on Google and found this link.
http://allenbrowne.com/casu-13.html

Allen Browne is an excellent resource and his tutorials and tools at top notch.

Good luck.

That's great... looks like an excellent place to refer to. Thanks again for the help.