Cartesain Query

J

Jeff

OK..I have been working on this query without much
success, I hope that someone can help me with this one.
I am sure that I need to use a Cartesain query but I am
having trouble making it work.
I need a query That shows a line for every date that
Click to expand...
corresponds with the checked boxes in the table entry.

Example:
These are the "Meeting Table" Fields:
EmployeeID, StartDate, EndDate, S,M,T,W,TH,F,SA (the last
7 are check boxes)


Meeting Table entries:
John Smith 10-1-03, 10-10-03, M,W,F (checked Boxes)
Jane Doe 10-1-03, 10-10-03, T,TH (checked Boxes)

desired query result:
John Smith 10-1-03
John Smith 10-3-03
John Smith 10-6-03
John Smith 10-8-03
John Smith 10-10-03
Jane Doe 10-2-03
Jane Doe 10-7-03
Jane Doe 10-9-03
 
M

[MVP] S. Clark

I think you may want to start by re-examining the definition of a Cartesian
or Cross Product.
Second, you may want to consider implementing some of the rules of
Normalization on your tables.

I can guarantee you this: Almost everything you try to do with this data,
in regards to Access queries, is going to be an uphill, hair-pulling, losing
battle.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
J

John Spencer (MVP)

I guess my earlier proposed solution did not work. Were there any error
messages, any results, no results?
 
J

John Verhagen

This worked for me, and you are right about using Cartesian query:
Create a table called Iota with long integer field Iota, and populate the
table with 100 records, with field iota having numbers from 1-100.
Then use the query below:
SELECT tblMeetingTable.EmployeeID, DateAdd("d",[StartDate],[Iota]-1) AS
WhatDate
FROM tblMeetingTable, Iota
WHERE (((DateAdd("d",[StartDate],[Iota]-1))<=[EndDate]) AND
((Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-*1 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[M]*2 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[T]*3 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[W]*4 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[TH]*5 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[F]*6 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[SA]*7))
ORDER BY tblMeetingTable.EmployeeID, DateAdd("d",[StartDate],[Iota]-1);
 
J

Jeff

-----Original Message-----
This worked for me, and you are right about using Cartesian query:
Create a table called Iota with long integer field Iota, and populate the
table with 100 records, with field iota having numbers from 1-100.
Then use the query below:
SELECT tblMeetingTable.EmployeeID, DateAdd("d", [StartDate],[Iota]-1) AS
WhatDate
FROM tblMeetingTable, Iota
WHERE (((DateAdd("d",[StartDate],[Iota]-1))<=[EndDate]) AND
((Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-*1 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[M]*2 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[T]*3 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[W]*4 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[TH]*5 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[F]*6 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[SA]*7))
ORDER BY tblMeetingTable.EmployeeID, DateAdd("d", [StartDate],[Iota]-1);

Jeff said:
OK..I have been working on this query without much
success, I hope that someone can help me with this one.
I am sure that I need to use a Cartesain query but I am
having trouble making it work.

corresponds with the checked boxes in the table entry.

Example:
These are the "Meeting Table" Fields:
EmployeeID, StartDate, EndDate, S,M,T,W,TH,F,SA (the last
7 are check boxes)


Meeting Table entries:
John Smith 10-1-03, 10-10-03, M,W,F (checked Boxes)
Jane Doe 10-1-03, 10-10-03, T,TH (checked Boxes)

desired query result:
John Smith 10-1-03
John Smith 10-3-03
John Smith 10-6-03
John Smith 10-8-03
John Smith 10-10-03
Jane Doe 10-2-03
Jane Doe 10-7-03
Jane Doe 10-9-03
Click to expand...


.
John, This looks great!.....if I need to show only the
Click to expand...

records of a specified month (or between specified dates)
what would be the best way.
 
J

Jeff

-----Original Message-----
This worked for me, and you are right about using Cartesian query:
Create a table called Iota with long integer field Iota, and populate the
table with 100 records, with field iota having numbers from 1-100.
Then use the query below:
SELECT tblMeetingTable.EmployeeID, DateAdd("d", [StartDate],[Iota]-1) AS
WhatDate
FROM tblMeetingTable, Iota
WHERE (((DateAdd("d",[StartDate],[Iota]-1))<=[EndDate]) AND
((Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-*1 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[M]*2 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[T]*3 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[W]*4 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[TH]*5 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[F]*6 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[SA]*7))
ORDER BY tblMeetingTable.EmployeeID, DateAdd("d", [StartDate],[Iota]-1);

Jeff said:
OK..I have been working on this query without much
success, I hope that someone can help me with this one.
I am sure that I need to use a Cartesain query but I am
having trouble making it work.

corresponds with the checked boxes in the table entry.

Example:
These are the "Meeting Table" Fields:
EmployeeID, StartDate, EndDate, S,M,T,W,TH,F,SA (the last
7 are check boxes)


Meeting Table entries:
John Smith 10-1-03, 10-10-03, M,W,F (checked Boxes)
Jane Doe 10-1-03, 10-10-03, T,TH (checked Boxes)

desired query result:
John Smith 10-1-03
John Smith 10-3-03
John Smith 10-6-03
John Smith 10-8-03
John Smith 10-10-03
Jane Doe 10-2-03
Jane Doe 10-7-03
Jane Doe 10-9-03
Click to expand...


.
Also...When I have a record that the dates are 1/1/03 to
Click to expand...

1/1/04 The query is only showing thru 4/9/03. Thank you
for your help with this problem. I have been struggling!
 
J

John Spencer (MVP)

Well, this part I can answer, Add more records the IOTA table - so that you have
366 records with the numbers from one to 366. If you need more than a year's
worth of days, then add more records to the IOTA table.
 
J

John Spencer (MVP)

You might TRY modifying the query to add a criteria clause against the
calculated field of WhatDate.

SELECT tblMeetingTable.EmployeeID,
DateAdd("d",[StartDate],[Iota]-1) AS WhatDate
FROM tblMeetingTable, Iota
WHERE ((((DateAdd("d",[StartDate],[Iota]-1))<=[EndDate]) AND
((Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-*1 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[M]*2 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[T]*3 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[W]*4 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[TH]*5 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[F]*6 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[SA]*7)))

AND DateAdd("d",[StartDate],[Iota]-1)
BETWEEN #1/1/03# AND #1/31/03#

ORDER BY tblMeetingTable.EmployeeID, DateAdd("d",[StartDate],[Iota]-1);
-----Original Message-----
This worked for me, and you are right about using Cartesian query:
Create a table called Iota with long integer field Iota, and populate the
table with 100 records, with field iota having numbers from 1-100.
Then use the query below:
SELECT tblMeetingTable.EmployeeID, DateAdd("d", [StartDate],[Iota]-1) AS
WhatDate
FROM tblMeetingTable, Iota
WHERE (((DateAdd("d",[StartDate],[Iota]-1))<=[EndDate]) AND
((Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-*1 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[M]*2 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[T]*3 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[W]*4 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[TH]*5 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[F]*6 Or
(Weekday(DateAdd("d",[StartDate],[Iota]-1)))=-[SA]*7))
ORDER BY tblMeetingTable.EmployeeID, DateAdd("d", [StartDate],[Iota]-1);

Jeff said:
OK..I have been working on this query without much
success, I hope that someone can help me with this one.
I am sure that I need to use a Cartesain query but I am
having trouble making it work.

I need a query That shows a line for every date that
corresponds with the checked boxes in the table entry.

Example:
These are the "Meeting Table" Fields:
EmployeeID, StartDate, EndDate, S,M,T,W,TH,F,SA (the last
7 are check boxes)


Meeting Table entries:
John Smith 10-1-03, 10-10-03, M,W,F (checked Boxes)
Jane Doe 10-1-03, 10-10-03, T,TH (checked Boxes)

desired query result:
John Smith 10-1-03
John Smith 10-3-03
John Smith 10-6-03
John Smith 10-8-03
John Smith 10-10-03
Jane Doe 10-2-03
Jane Doe 10-7-03
Jane Doe 10-9-03
Click to expand...


.
John, This looks great!.....if I need to show only the
Click to expand...

records of a specified month (or between specified dates)
what would be the best way.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cartesian Query 1
Continuous Dates 5
Date Calculation.,. 3
Combine 2 tables for a report 2
Group By Query Problem 4
Select Max 1
joining results 3
Who's due for a shot? 2

Top