Two tables, one link of date, multiple entries in one table per da

[Harriet]

I am trying to create a query to run two tables; sinle relationship of
transaction date but one table may have multiple entries of a transaction
while the other has a single entry for the transaction for the same date.

Now, the query reads the table with the single entry and gives a line for
the other tables multiple entries and repeats the transaction ID on the
single table, mulitiple times or for each transaction ID in the multiple
table.

Example: table 1 has transaction ID 1234 for date 5/1/09 and table 2 has
transaction 9114, 9115, 9116 for date 5/1/09.

Currently, the result is record 1234 9114, 1234 9115, 1234 9116 and I just
want to see a record for each transaction id on a single line or row.

Eventually this needs to be in a report...do I need to somehow manipulate
there? I've tried to group there and it isn't working out.

Help.

Harriet

 

[Jerry Whittle]

You probably need a Union query to join the two tables. Something like

Select [Transaction ID], [Date] From Table1
UNION ALL
Select [Transaction ID], [Date] From Table2 ;

 

[Harriet]

Thanks Jerry...i think I'm getting there.

I want to add a date parameter and her is my sQL stmt, if you can make sense
of it and tell me where I might be wrong b/c I'm bringing back all the rows
of data in the table and not the specific date:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tIncoming Trace].[Date Contacted]) Like '5/1/2009'));

I want this query to show me the Incoming trace # and it's associated date
on a separate line from the Outgoing trace #and associated date. There are
only two records for incoming and maybe 12 records for outgoing and I'm
getting a combination of the one incoming associating to each outgoing and
then the 2nd incoming associating to each outgoing, some kind of
permutation/combination thing going on!

Once I get this to work I can add other fields that I actually need

Make sense?

You probably need a Union query to join the two tables. Something like

Select [Transaction ID], [Date] From Table1
UNION ALL
Select [Transaction ID], [Date] From Table2 ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to create a query to run two tables; sinle relationship of
transaction date but one table may have multiple entries of a transaction
while the other has a single entry for the transaction for the same date.

Now, the query reads the table with the single entry and gives a line for
the other tables multiple entries and repeats the transaction ID on the
single table, mulitiple times or for each transaction ID in the multiple
table.

Example: table 1 has transaction ID 1234 for date 5/1/09 and table 2 has
transaction 9114, 9115, 9116 for date 5/1/09.

Currently, the result is record 1234 9114, 1234 9115, 1234 9116 and I just
want to see a record for each transaction id on a single line or row.

Eventually this needs to be in a report...do I need to somehow manipulate
there? I've tried to group there and it isn't working out.

Help.

Harriet

 

[Harriet]

Oh, I'm only getting the incoming data and nothing for the outgoing, all of
the rows of data.

 

[John W. Vinson]

I want to add a date parameter and her is my sQL stmt, if you can make sense
of it and tell me where I might be wrong b/c I'm bringing back all the rows
of data in the table and not the specific date:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tIncoming Trace].[Date Contacted]) Like '5/1/2009'));

I want this query to show me the Incoming trace # and it's associated date
on a separate line from the Outgoing trace #and associated date. There are
only two records for incoming and maybe 12 records for outgoing and I'm
getting a combination of the one incoming associating to each outgoing and
then the 2nd incoming associating to each outgoing, some kind of
permutation/combination thing going on!

A Date is NOT a string, and the LIKE operator is needed only when you're using
"wildcards" to search a partial string. If Date Contacted is in fact a
date/time field your WHERE clause should be

WHERE ((([tIncoming Trace].[Date Contacted])=#5/1/2009#))

and it should probably be in both SELECT clauses, using the appropriate table
name in each case::

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
WHERE ((([tIncoming Trace].[Date Contacted])=#5/1/2009#))
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tOutgoing Trace].[Date Contacted])=#5/1/2009#));

This should show 14 rows - two from Incoming and all 12 from Outgoing, if
that's how many records there are for that date.

 

[Harriet]

This works great and I get it now...I have other fields to add and I just
need to add them to both. Thank you so much!

I want to add a date parameter and her is my sQL stmt, if you can make sense
of it and tell me where I might be wrong b/c I'm bringing back all the rows
of data in the table and not the specific date:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tIncoming Trace].[Date Contacted]) Like '5/1/2009'));

I want this query to show me the Incoming trace # and it's associated date
on a separate line from the Outgoing trace #and associated date. There are
only two records for incoming and maybe 12 records for outgoing and I'm
getting a combination of the one incoming associating to each outgoing and
then the 2nd incoming associating to each outgoing, some kind of
permutation/combination thing going on!

A Date is NOT a string, and the LIKE operator is needed only when you're using
"wildcards" to search a partial string. If Date Contacted is in fact a
date/time field your WHERE clause should be

WHERE ((([tIncoming Trace].[Date Contacted])=#5/1/2009#))

and it should probably be in both SELECT clauses, using the appropriate table
name in each case::

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
WHERE ((([tIncoming Trace].[Date Contacted])=#5/1/2009#))
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tOutgoing Trace].[Date Contacted])=#5/1/2009#));

This should show 14 rows - two from Incoming and all 12 from Outgoing, if
that's how many records there are for that date.

 

[Harriet]

John,
If you have a date field that is Date/Time, can you select that date in the
query we've been working on?

The following works when the Date Contacted is just a text date but changes
were made in the table to make the date a Date/Time field, with default value
'Now()' and the table contains both text Dates and Date/Time and the latter,
the query doesn't work:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Package
Status], [tIncoming Trace].[Trace Status]
FROM [tIncoming Trace]
WHERE (([tIncoming Trace].[Date Contacted] Between [Start Date] and [End
Date]))
UNION ALL SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing
Trace].[Package Status], [tOutgoing Trace].[Trace Status]
FROM [tOutgoing Trace]
WHERE (([tOutgoing Trace].[Date Contacted] Between [Start Date] and [End
Date]));

Any suggestions I sure would appreciate!

I want to add a date parameter and her is my sQL stmt, if you can make sense
of it and tell me where I might be wrong b/c I'm bringing back all the rows
of data in the table and not the specific date:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tIncoming Trace].[Date Contacted]) Like '5/1/2009'));

I want this query to show me the Incoming trace # and it's associated date
on a separate line from the Outgoing trace #and associated date. There are
only two records for incoming and maybe 12 records for outgoing and I'm
getting a combination of the one incoming associating to each outgoing and
then the 2nd incoming associating to each outgoing, some kind of
permutation/combination thing going on!

A Date is NOT a string, and the LIKE operator is needed only when you're using
"wildcards" to search a partial string. If Date Contacted is in fact a
date/time field your WHERE clause should be

WHERE ((([tIncoming Trace].[Date Contacted])=#5/1/2009#))

and it should probably be in both SELECT clauses, using the appropriate table
name in each case::

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Date
Contacted] FROM [tIncoming Trace]
WHERE ((([tIncoming Trace].[Date Contacted])=#5/1/2009#))
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Date
Contacted] FROM [tOutgoing Trace]
WHERE ((([tOutgoing Trace].[Date Contacted])=#5/1/2009#));

This should show 14 rows - two from Incoming and all 12 from Outgoing, if
that's how many records there are for that date.

 

[John W. Vinson]

John,
If you have a date field that is Date/Time, can you select that date in the
query we've been working on?

The following works when the Date Contacted is just a text date but changes
were made in the table to make the date a Date/Time field, with default value
'Now()' and the table contains both text Dates and Date/Time and the latter,
the query doesn't work:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Package
Status], [tIncoming Trace].[Trace Status]
FROM [tIncoming Trace]
WHERE (([tIncoming Trace].[Date Contacted] Between [Start Date] and [End
Date]))
UNION ALL SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing
Trace].[Package Status], [tOutgoing Trace].[Trace Status]
FROM [tOutgoing Trace]
WHERE (([tOutgoing Trace].[Date Contacted] Between [Start Date] and [End
Date]));

Instead of

Between [Start Date] AND [End Date]

try

= CDate([Start Date]) AND < DateAdd("d", 1, CDate([End Date]))

This will pick up the records between the start and end of the day on End
Date, and cover for badly formatted date entries.

 

[Harriet]

I substituted the following and got a syntax error of missing operator:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Package
Status], [tIncoming Trace].[Trace Status]
FROM [tIncoming Trace]
WHERE (([tIncoming Trace].[Date Contacted] >= CDate([Start Date]) AND <
DateAdd("d", 1, CDate([End Date]))
UNION ALL
SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing Trace].[Package
Status], [tOutgoing Trace].[Trace Status]
FROM [tOutgoing Trace]
WHERE (([tOutgoing Trace].[Date Contacted] >= CDate([Start Date]) AND <
DateAdd("d", 1, CDate([End Date]));

Not sure now what is wrong...

Thank you!!!

John,
If you have a date field that is Date/Time, can you select that date in the
query we've been working on?

The following works when the Date Contacted is just a text date but changes
were made in the table to make the date a Date/Time field, with default value
'Now()' and the table contains both text Dates and Date/Time and the latter,
the query doesn't work:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Package
Status], [tIncoming Trace].[Trace Status]
FROM [tIncoming Trace]
WHERE (([tIncoming Trace].[Date Contacted] Between [Start Date] and [End
Date]))
UNION ALL SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing
Trace].[Package Status], [tOutgoing Trace].[Trace Status]
FROM [tOutgoing Trace]
WHERE (([tOutgoing Trace].[Date Contacted] Between [Start Date] and [End
Date]));

Instead of

Between [Start Date] AND [End Date]

try

= CDate([Start Date]) AND < DateAdd("d", 1, CDate([End Date]))

This will pick up the records between the start and end of the day on End
Date, and cover for badly formatted date entries.

 

[Harriet]

specifically highlighting the less than sign after AND:

WHERE (([tIncoming Trace].[Date Contacted] >= CDate([Start Date]) AND <
DateAdd("d", 1, CDate([End Date]))

John,
If you have a date field that is Date/Time, can you select that date in the
query we've been working on?

The following works when the Date Contacted is just a text date but changes
were made in the table to make the date a Date/Time field, with default value
'Now()' and the table contains both text Dates and Date/Time and the latter,
the query doesn't work:

SELECT [tIncoming Trace].[Incoming Trace Number], [tIncoming Trace].[Package
Status], [tIncoming Trace].[Trace Status]
FROM [tIncoming Trace]
WHERE (([tIncoming Trace].[Date Contacted] Between [Start Date] and [End
Date]))
UNION ALL SELECT [tOutgoing Trace].[Outgoing Trace Number], [tOutgoing
Trace].[Package Status], [tOutgoing Trace].[Trace Status]
FROM [tOutgoing Trace]
WHERE (([tOutgoing Trace].[Date Contacted] Between [Start Date] and [End
Date]));

Instead of

Between [Start Date] AND [End Date]

try

= CDate([Start Date]) AND < DateAdd("d", 1, CDate([End Date]))

This will pick up the records between the start and end of the day on End
Date, and cover for badly formatted date entries.

 

[John W. Vinson]

I substituted the following and got a syntax error of missing operator:

You need to include the field being searched twice: sorry I didn't make that
clear!

WHERE (([tOutgoing Trace].[Date Contacted] >= CDate([Start Date]) AND
[tOutgoing Trace].[Date Contacted] < DateAdd("d", 1, CDate([End Date]));

 

[Harriet]

Thank you John. You are awesome! I found one more tweak to do, an extra
parentheses and got it to finally work.

I may need to call on you in the future...

Harriet

I substituted the following and got a syntax error of missing operator:

You need to include the field being searched twice: sorry I didn't make that
clear!

WHERE (([tOutgoing Trace].[Date Contacted] >= CDate([Start Date]) AND
[tOutgoing Trace].[Date Contacted] < DateAdd("d", 1, CDate([End Date]));