Extraction of Dates

  • Thread starter Thread starter Shell
  • Start date Start date
S

Shell

Hello...I would appreciate any help. I have database that I am creating and
one of the tables contains SessionId (unique), StartDate, and an EndDate.
This data comes from a data logger. There is also another table connected to
this table that records a sound code at night, and a date if sounds were
heard (within those session dates). However...if there was no sound...then
that date does not get input. What I would like to see is an output of all
the dates (from the StartDate to the EndDate for each session), whether or
not there was a sound or not. How do I generate a populate a table/query
with this? Stumped! I put an example of what I would like to see at the
bottom

SessionId StartDate EndDate
001 02/11/2010 02/16/2010
002 02/23/2010 02/27/2010
003 03/03/2010 03/13/2010

UniqueTWId SessionId Date SoundCode
45 001 02/11/2010 A
46 001 02/15/2010 D
47 002 02/24/2010 A
48 002 02/25/2010 E
49 002 02/26/2010 Z
50 003 03/05/2010 A


What I would like is some like this (ex. Session 001 only)
SessionId Date SoundCode
001 02/11/2010 A
001 02/12/2010 Null
001 02/13/2010 Null
001 02/14/2010 Null
001 02/15/2010 D
 
Hello...I would appreciate any help. I have database that I am creating and
one of the tables contains SessionId (unique), StartDate, and an EndDate.
This data comes from a data logger. There is also another table connected to
this table that records a sound code at night, and a date if sounds were
heard (within those session dates). However...if there was no sound...then
that date does not get input. What I would like to see is an output of all
the dates (from the StartDate to the EndDate for each session), whether or
not there was a sound or not. How do I generate a populate a table/query
with this? Stumped! I put an example of what I would like to see at the
bottom

SessionId StartDate EndDate
001 02/11/2010 02/16/2010
002 02/23/2010 02/27/2010
003 03/03/2010 03/13/2010

UniqueTWId SessionId Date SoundCode
45 001 02/11/2010 A
46 001 02/15/2010 D
47 002 02/24/2010 A
48 002 02/25/2010 E
49 002 02/26/2010 Z
50 003 03/05/2010 A


What I would like is some like this (ex. Session 001 only)
SessionId Date SoundCode
001 02/11/2010 A
001 02/12/2010 Null
001 02/13/2010 Null
001 02/14/2010 Null
001 02/15/2010 D
Click to expand...

Thanks for the clear and helpful presentation of the problem!

You'll need an auxiliary table to do this: let's call it AllDates, with one
field TheDate (note that Date is a reserved word and a bad choice for a
fieldname). Fill this table with one record per date covering the full span of
dates in your sessions (Excel Insert... Fill Series is a handy way to build
this). Assuming that your tables are named Sessions and Sounds respectively,
first create a query qryAllDates:

SELECT AllDates.TheDate, Sounds.SessionID, Sounds.SoundCode
FROM AllDates LEFT JOIN Sounds
ON AllDates.TheDate = Sounds.[Date];

This query will have one row for each date, whether there was a sound heard on
that date or not. Then create a non-equi join query joining Sessions to
qryAllDates:

SELECT Sessions.SessionID, qryAllDates.TheDate, qryAllDates.SoundCode
FROM Sessions INNER JOIN qryAllDates
ON qryAllDates.TheDate >= Sessions.StartDate
AND qryAllDates.TheDate <= Sessions.EndDate
WHERE qryAllDates.SessionID = Sessions.SessionID
OR qryAllDates.SessionID IS NULL;
 
It looks like you have a

Table of sessions

A table of instances of a sound code occurring within a session.

The first thing that you are going to have to determine ()and tell us) is
whether or not there can be multiple records in the second table for a single
session. I'm assuming that the answer is yes. (if it's "no" then your
problem is very simple, you could even combine the two tables into one)

So now you have to decide EXACTLY what you want to have constitute a "line"
in your final list.

If your answer is "one line per session" then you have a conceptual problem.
If there are many recording within one session, exactly what do you want to
see in that ONE spot in the last column? I believe that that question will
force a rethinking of the "EXACTLY what you want....." question. And then
you could tell us the answer and we could help you.

Hope that helps a little.
 
I should have said "multiple sounds on one date of one session", not multiple
sounds during one session.
 
Back
Top