Must I create a record for each value in a field in this table?

[John]

I am trying to get some data from two tables.

Tbl_Dates has multiple values in the WatchOffGroup field.
I know that this is not a best practice - but I have inherited this
table.

Given this, I need help with a select query that results in producing
data from these two tables:

tbl_Members
MemberID
Lname
WatchOff

Sample Data

MemberID Lname WatchOffGroup
1 Jones 4
2 Smith 7
3 Green 1

***************

tbl_Dates
DateID
Date
WatchOff

Sample Data

DateID Date WatchOffGroups
1 9/1/05 12
2 9/2/05 23
3 9/3/05 34
4 9/4/05 56

The problem data is in the WatchOffGroups field above.
Group 1 and Group 2 are both in the same Watch Off Group field.
I know it would be better if there were a unique record for each watch
off group for each date but is there a way I can use this data. In the
real DB there are 22 groups: 8 are off each day and 14 are on each day
- that would be 22 records per day x 365 days x 3 years of data I would
have to create. I'm hoping to avoid that.

****************

I want to be able to choose any date and then return the names of the

members who have their watch off group listed in the tbl_Dates.WatchOff
field for the selected date.

Example:
If I choose the date 9/1/05, there are two listed watch off groups for
that date listed in tbl_Dates: group and group 2.

I want to be able to produce a result from a query that lists Member
Green's Last name because he is in Group 1 which is one of the watch
off groups listed for 9/1/05 in the Dates table.

If I choose 9/3/05, I should see Jones in the query result because
Jones is in Watch Off Group 4.

Any suggestions would be appreciated.

Thanks
John

 

[David S via AccessMonster.com]

That's a tough one there. One thing you could do is create a query that
splits out the WatchOffGroups like qry_Dates:
SELECT tbl_Dates.*, Left([WatchOffGroups],1) AS WatchOffGroup1, Mid(
[WatchOffGroups],2,1) AS WatchOffGroup2
FROM tbl_Dates;

Then you can use each column to get the relevant members
qry_WatchOffGroups1:
SELECT qry_Dates.Date, tbl_Members.WatchOffGroup, tbl_Members.MemberID,
tbl_Members.Lname
FROM qry_Dates INNER JOIN tbl_Members ON qry_Dates.WatchOffGroup1 =
tbl_Members.WatchOffGroup;

qry_WatchOffGroups2:
SELECT qry_Dates.Date, tbl_Members.WatchOffGroup, tbl_Members.MemberID,
tbl_Members.Lname
FROM qry_Dates INNER JOIN tbl_Members ON qry_Dates.WatchOffGroup2 =
tbl_Members.WatchOffGroup;

And then you can put all of those records back together in qry_WatchOffGroups:

SELECT * FROM qry_WatchOffGroups1
UNION SELECT * FROM qry_WatchOffGroups2;

The problem with this is that you would need qry_WatchOffGroups* for as many
groups as can appear in that field. With just two, it's not too bad, but if
it can have any number of groups in this field, you'd have to take a guess as
to a maximum. And since it's relying on a union query at the last stage, the
performance may start to suffer as well... hope that helps, though.

 

[John]

Thanks for your response, David.

I ended up copying tbl_Dates to Excel and using the Data, Text to
Columns utility and some copy and paste to create an individual date
record for each of the 8 WatchOffGroups for each day of the year.
Then I went back into Access and imported the spreadsheet with the new
records.

Now, I have to produce two queries:

1. Produce a list of members who are watch off on any given date. (I
can do this)

2. Produce a list of members who are NOT watch off (watch on) on any
given date. (This one has stumped me and I need HELP)

Something like...

SELECT tbl_Member.Lname, tbl_Member.WatchOffGroup, tbl_Dates.Date,
tbl_Dates.DOW
FROM tbl_Member, tbl_Dates
WHERE tbl_Member.WatchOffGroup *does not equal*
tbl_Dates.WatchOffGroup;

*I couldn't link the tables with a negative like <> or != so I don't
know how to proceed.

TIA
John
Any suggestions?

 

[David S via AccessMonster.com]

Hi John,

What you can do is take the output from your first query that tells you who
was Watch Off on a given date, and use it to figure out who was Watch On on
that date.

Your first query will need to return the MemberID for this to work, so
WatchOffByDate should look something like:
SELECT tbl_Members.MemberID, tbl_Members.Lname, tbl_Dates.WatchOffGroup,
tbl_Dates.Date, tbl_Dates.DOW
FROM tbl_Dates INNER JOIN tbl_Members ON tbl_Dates.WatchOffGroup =
tbl_Members.WatchOffGroup
WHERE (((tbl_Dates.Date)=[Enter Date]));

Then, you can use an outer join to find out every other Member who isn't in
this list:
SELECT tbl_Members.MemberID, tbl_Members.Lname, tbl_Members.WatchOffGroup
FROM WatchOffByDate RIGHT JOIN tbl_Members ON WatchOffByDate.MemberID =
tbl_Members.MemberID
WHERE (((WatchOffByDate.MemberID) Is Null));

The outer join (a right join in this case) means you get all records from the
tbl_Members table, and not just those that have matches; by then asking for
those where the the MemberID from the left table is Null, you effectively ask
for all the records in tbl_Members that don't have matches in WatchOffByDate.

Hope that helps. It's about to become the weekend here in Australia, so it
won't be until Monday 14/11 that I check this forum again....
David...

 

[John]

Thanks for your time and interest, David.

I got a successful and eligant solution from another source here at San
Francisco Airport.

SELECT DISTINCT tbl_Member.Lname, tbl_Member.WatchOff, tbl_Dates.Date,
tbl_Dates.DOW
FROM tbl_Dates ,tbl_Member
WHERE tbl_Dates.date= [Enter OT Date]
and not exists
(select 1 from tbl_Dates a
where tbl_Member.WatchOff = a.WatchOff
and a.Date=[Enter OT Date]);

Do you know what the "select 1" means in the subquery?
I have never seen it before.

Thanks
John

 

[John Vinson]

SELECT DISTINCT tbl_Member.Lname, tbl_Member.WatchOff, tbl_Dates.Date,
tbl_Dates.DOW
FROM tbl_Dates ,tbl_Member
WHERE tbl_Dates.date= [Enter OT Date]
and not exists
(select 1 from tbl_Dates a
where tbl_Member.WatchOff = a.WatchOff
and a.Date=[Enter OT Date]);

Do you know what the "select 1" means in the subquery?
I have never seen it before.

The NOT EXISTS predicate just checks to see if the subquery returns
any value at all. It doesn't matter what that value is; the person who
wrote this just arbitrarily had the query return a constant. One could
just as well have used (SELECT tblMember.WatchOff ... ) or any other
field in the query.

John W. Vinson[MVP]