Find unmached records / create table

[Guest]

Hello:

I hope you can help me with this.

I have a Table1 that contains, eg.:

CardId Date Time
10 15/01/05 12:00
10 15/01/05 14:00

11 20/01/05 10:00

12 15/01/05 10:00
12 20/01/05 13:00

so I have two dates in this table - 15/01/05 and 20/01/05)

Table2 (master id) table contains:
Card Id
10
11
12
13

The research question is to find records which have Card Id in Table 2
(master) and don't have a record for any of the unique dates in Table 1.
Basically, it is to compare two tables and to create a table with the result

Final result should be a table containing:

Card ID Date
10 20/01/05 (Card Id 10 has no record for this day in table1)
11 15/01/04 (Card Id 11 has no record for this day in table1)

13 15/01/04 (no record for any of these dates)
13 20/01/04 "

ID 12 is ok as it has a record of both dates and should not be added to the
query result.

It was working ok when I had a file with just one date in Table1 for all the
records - I was able to found unmached records in a couple or more of
queries. Now, the file may contain mulitiple dates and it does not work any
more.

Can you help please?

Danka

Thanks.

 

[Guest]

Hi, Danka.

Date and Time are Reserved words, so avoid using them for names. Table1 and
Table2 aren't very helpful in describing what they contain, especially when
there are 20 tables or more with the same naming convention. I'll give you
an example of how to find records that don't exist, but I'll use different
names than you have.

Two table structures:

tblCards:
CardID, AutoNumber, primary key
PID, Long, foreign key to tblPersonnel's PID

tblCheckOuts:
COID, AutoNumber, primary key
CardID, Long, foreign key to tblCards' CardID
CheckOutDate, Date/Time
CheckOutTime, Date/Time

The only two columns you'll need to worry about for the queries are CardID
and CheckOutDate, so don't worry too much about the other columns. They're
just for illustration of the table structures.

Create a query that contains a Cartesian Join (normally not used in queries,
but in this special situation we want to list all possible combinations):

SELECT tblCards.CardID, Q.CheckOutDate
FROM tblCards,
(SELECT DISTINCT CheckOutDate
FROM tblCheckOuts) AS Q
ORDER BY tblCards.CardID, Q.CheckOutDate;

Save it and name it qryAllPossibleChkOutDates. Create another new query:

SELECT Q.CardID, Q.CheckOutDate
INTO tblNonChkOuts
FROM tblCheckOuts AS CO RIGHT JOIN
qryAllPossibleChkOutDates AS Q ON (CO.CardID = Q.CardID)
AND (CO.CheckOutDate = Q.CheckOutDate)
WHERE (IsNull(CO.CardID) = TRUE)
ORDER BY Q.CardID, Q.CheckOutDate;

Run this query and a new table, tblNonChkOuts, will be created. It will
contain any CardID's issued with the dates they weren't used for checkouts.
Now, this query creates a new table, but most of the time calculated values
(such as these records that don't exist) would be used in a query to display
these values, not store them. If you just want to display them, then the
final query would be:

SELECT Q.CardID, Q.CheckOutDate
FROM tblCheckOuts AS CO RIGHT JOIN
qryAllPossibleChkOutDates AS Q ON (CO.CardID = Q.CardID)
AND (CO.CheckOutDate = Q.CheckOutDate)
WHERE (IsNull(CO.CardID) = TRUE)
ORDER BY Q.CardID, Q.CheckOutDate;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

['69 Camaro]

Hi, Danka.

Date and Time are Reserved words, so avoid using them for names. Table1 and
Table2 aren't very helpful in describing what they contain, especially when
there are 20 tables or more with the same naming convention. I'll give you
an example of how to find records that don't exist, but I'll use different
names than you have.

Two table structures:

tblCards:
CardID, AutoNumber, primary key
PID, Long, foreign key to tblPersonnel's PID

tblCheckOuts:
COID, AutoNumber, primary key
CardID, Long, foreign key to tblCards' CardID
CheckOutDate, Date/Time
CheckOutTime, Date/Time

The only two columns you'll need to worry about for the queries are CardID
and CheckOutDate, so don't worry too much about the other columns. They're
just for illustration of the table structures.

Create a query that contains a Cartesian Join (normally not used in queries,
but in this special situation we want to list all possible combinations):

SELECT tblCards.CardID, Q.CheckOutDate
FROM tblCards,
(SELECT DISTINCT CheckOutDate
FROM tblCheckOuts) AS Q
ORDER BY tblCards.CardID, Q.CheckOutDate;

Save it and name it qryAllPossibleChkOutDates. Create another new query:

SELECT Q.CardID, Q.CheckOutDate
INTO tblNonChkOuts
FROM tblCheckOuts AS CO RIGHT JOIN
qryAllPossibleChkOutDates AS Q ON (CO.CardID = Q.CardID)
AND (CO.CheckOutDate = Q.CheckOutDate)
WHERE (IsNull(CO.CardID) = TRUE)
ORDER BY Q.CardID, Q.CheckOutDate;

Run this query and a new table, tblNonChkOuts, will be created. It will
contain any CardID's issued with the dates they weren't used for checkouts.
Now, this query creates a new table, but most of the time calculated values
(such as these records that don't exist) would be used in a query to display
these values, not store them. If you just want to display them, then the
final query would be:

SELECT Q.CardID, Q.CheckOutDate
FROM tblCheckOuts AS CO RIGHT JOIN
qryAllPossibleChkOutDates AS Q ON (CO.CardID = Q.CardID)
AND (CO.CheckOutDate = Q.CheckOutDate)
WHERE (IsNull(CO.CardID) = TRUE)
ORDER BY Q.CardID, Q.CheckOutDate;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember
that questions answered the quickest are often from those who have a history
of rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hello:

I want to thank for the solution. It works perfectly and provides the right
result . I am struggling to understand how the Cartesian join works and what
excatly is happening when the second query is run...