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.

 

[wackerpf]

Btw, here is a quick snapshot of the relationship tree so you can visualize the table structure.
<a href="http://twitpic.com/b4cr5" title="Share photos on twitter with Twitpic"><img src="http://twitpic.com/show/thumb/b4cr5.jpg" width="150" height="150" alt="Share photos on twitter with Twitpic"></a>

Again, any help is appreciated, thanks.

 

[jackd]

wackerpf,

I think your model may need some adjustment. But first a comment on tables and relationships. Your tblClients has primary key CaseId. This is a concern. Most often a `Client table` would have a ClientId as a primary key. Your tblClient only allows for 1 `Notes`. I don`t think that is what you want.

My suggestion would be to define for yourself what each of your tables really represents. You may even want to describe in plain English what your `application`is trying to do. You`d be surprised at the value of describing the things involved and each of the 'attributes' you are interested in.

For example what is a Case? What is an Office Visit? What is a Client? Could a Client have an officeVisit independent of a Case? You could determine the number of OfficeVisits by means of a query. There is no need to record a Visit number. Do you want to use this for a Client list - phone , address, etc. Do you want to possibly record a Note for each OfficeVisit, but not necessarily?

Let us know if this is useful.
Good luck.

 

[wackerpf]

Hey I appreciate your reply.

Honestly, I didn't list all the fields from that clients table (the notes field I just happened to leave in there). And I did label that as "CaseID" on purpose... the database is for a study, and no, there cannot be a client without a case... they are the case... and there will be no office visits independent of a case (as if they were, they would not be part of the study, and if they are part of the study, they must had a CaseID. The remaining details of the Clients table will be filled in with time, as they are just non-essential info fields about each client.

An office visit consists of treatments/doses being administered and therefore the date is important, and is not necessarily consistant with every case. The whole point of this query is for data analysis. I need to be able to select a client and see a comprehensive list of data values over the 7 days prior to each office visit. So, a client will come in on June 9 and June 30... I need the database to know these values, which is why I made the tbl just for office visits. I then need the database to list every data vale from tblData for that client with a DateTime value on, or within the last 7 days of, the dates (plural) listed in the Office Visits table.

Any suggestions?

Let me know if you need more details. I'm greatful for the response so far.

 

[jackd]

Hey I appreciate your reply.

Honestly, I didn't list all the fields from that clients table (the notes field I just happened to leave in there). And I did label that as "CaseID" on purpose... the database is for a study, and no, there cannot be a client without a case... they are the case... and there will be no office visits independent of a case (as if they were, they would not be part of the study, and if they are part of the study, they must had a CaseID. The remaining details of the Clients table will be filled in with time, as they are just non-essential info fields about each client.

An office visit consists of treatments/doses being administered and therefore the date is important, and is not necessarily consistant with every case. The whole point of this query is for data analysis. I need to be able to select a client and see a comprehensive list of data values over the 7 days prior to each office visit. So, a client will come in on June 9 and June 30... I need the database to know these values, which is why I made the tbl just for office visits. I then need the database to list every data vale from tblData for that client with a DateTime value on, or within the last 7 days of, the dates (plural) listed in the Office Visits table.

Any suggestions?

Let me know if you need more details. I'm greatful for the response so far.

What does datavalue represent? You mention treatments/doses but I don't see any "fields" to record same- am I missing something?

If each CaseId, during an OfficeVisit is recorded with Data and Time
, then you can use a query to identify all, or a specific range or individual OfficeVisit for that CaseId.

What is the question(s) you will be asking of the data?

These types of questions can be readily answered by querying your data - as long as the tables are structured properly.

Select All Office Visits for CaseId = 999

Select CaseId, name for OfficeVisits between June 10 2:30 and Jun 30 6:00

 

[wackerpf]

Select CaseId, name for OfficeVisits between June 10 2:30 and Jun 30 6:00

This type of statement I understand. My problem is that I can't enter in static values like that, since the time periods being looks at are different depending on the client. The treatments don't matter here (it is a research study and the clients receiving treatment vs placebo are not even known until the end). The DataValue field is just a positive integer. tblData will have records like:<table border=1><tr><td><u>CaseID</u></td><td><u>DateTime</u></td><td><u>DataValue</u></td></tr>
<tr><td>63</td><td>8/20/2008 6:01:00 AM</td><td>2</td></tr>
<tr><td>63</td><td>8/20/2008 6:02:00 AM</td><td>0</td></tr>
<tr><td>63</td><td>8/20/2008 6:03:00 AM</td><td>0</td></tr>
<tr><td>63</td><td>8/20/2008 6:04:00 AM</td><td>25</td></tr>
<tr><td>63</td><td>8/20/2008 6:05:00 AM</td><td>234</td></tr>
<tr><td>63</td><td>8/20/2008 6:06:00 AM</td><td>3</td></tr>
<tr><td>63</td><td>8/20/2008 6:07:00 AM</td><td>2635</td></tr>
<tr><td>63</td><td>8/20/2008 6:08:00 AM</td><td>0</td></tr>
<tr><td>63</td><td>8/20/2008 6:09:00 AM</td><td>0</td></tr></table>...This goes on for literally millions of records, as a value is recorded every minute over roughly a 20 week span for each client, of which there are dozens of (Therefore I set the PK as CaseID <i>and</i> DateTime, as it is IMPOSSIBLE for one client to have two data points at one given time... if there are, it should not be allowed as something went wrong).

Each client begins the study at a different time (recorded as the first office visit, and will also coincide with the earliest DateTime data point for that client). Each client then comes in periodically for treatment, w/ that date recorded on the office visits table.

So say a client is in the middle of the study and comes in June 14 and June 29... These two dates are recorded in the office visits table (recorded only as a date, not a time... so the time in the field defaults to 12 AM which is fine, and no, an office visit is never going to be multiple days in length). When they come in, their data is imported into tblData looking like shown in the table above.

In the end, I need to <i>have</i> a record of <i>every</i> data point for good record keeping purposes, HOWEVER, when doing statistical analysis on that data, I am <i>only concerned with the data points from the 7 days prior to each office visit</i>. So if the client came in on June 14 and June 29, I need the query to reference that list of dates from tblOfficeVisits and select only records where tblData.DateTime falls between June 7 - June 14, and June 22 - June 29 (as well as any other 7 day blocks prior to any other office visits).

Any suggestions on maybe how my tables need to be joined, or how exactly to enter the queries in the QBE editor? Do I need to use domain aggregate functions? When I try to enter a criteria for tblData.DateTime based on tblOfficeVisits.VisitDate I always get redundant data depending on how many office visits there have been (like a Cartesian product).

 

[nuclar2009]

The view joins events to recurrence_dates table like so: ... SELECT * FROM recurring_events WHERE user_id = 2 AND starts_at BETWEEN ... My first simple hack (obvious and ugly, of course; redundant data. .... And here is the EXPLAIN ANALYZE output for the same query, but without the date comparisons in the WHERE :Both fields are in the same table. I can use SELECT and COUNT to count how many have ... Join Date: Jan 2007. Location: sleeping with my imagination .... But I would like to have one query to combine these two . ... replaced with a single space - no point in sending redundant data to something that assword. Spinner_mac_white. Trouble logging in? .... Date Added. 09/21/2008 ... Joins & Sub-queries Oracle recognizes that you may want data that resides in ... thus resulting in unnecessary "redundant data". Types of Join There are four types ... Table aliases can also be used in the SELECT clause and can be of ...This will be a lookup list that allows them to select their manager ... like later on as well) This will alleviate redundant data by not having ... The values that I want though are in the same table (this is probably why the self-<wbr>join query you ... profdev, create a query based on the HRIS table, ...
































-----------------------
<table x:str="" style="border-collapse: collapse; width: 529pt;" border="0" cellpadding="0" cellspacing="0" width="705"><col style="width: 529pt;" width="705"><tr style="height: 12.75pt;" height="17"> <td class="xl61" style="height: 12.75pt; width: 529pt;" height="17" width="705">teen ***** rakeback at Absolute</td> </tr></table>

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[jackd]

wackerpf,

Please explain the 2 dates related to OfficeVisits.

"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)"

In your example the Client would have 4 visits -- so what 2 dates are you really dealing with in your query?

Sorry if I'm missing something obvious.

 

[wackerpf]

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).

 

[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).

Try this query and see if it's what you are looking for. Hope it's helpful. I'm using your table structures and relationships.

This allows you to Enter a CaseId, then it will get all DateTimes and Data Values that are <= 7 days from an Office Visit for that Client(CaseId).

SELECT tblClients.CaseId, tblData.DateTime, tblOfficeVisits.VisitDate, tblData.DataValue, DateDiff("d",[DateTime],[VisitDate]) AS NumDays
FROM (tblClients INNER JOIN tblData ON tblClients.CaseId = tblData.CaseId) INNER JOIN tblOfficeVisits ON tblClients.CaseId = tblOfficeVisits.CaseId
WHERE (((tblClients.CaseId)=[Enter CaseId]) AND ((DateDiff("d",[DateTime],[VisitDate]))<=7 And (DateDiff("d",[DateTime],[VisitDate]))>=0))
ORDER BY tblClients.CaseId, tblOfficeVisits.VisitDate;