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

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

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.


"Harriet" wrote:

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

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?

"Jerry Whittle" wrote:

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.


"Harriet" wrote:

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

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

"Harriet" wrote:

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

On Thu, 10 Sep 2009 14:44:06 -0700, Harriet
wrote:

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 [MVP]

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!

"John W. Vinson" wrote:

On Thu, 10 Sep 2009 14:44:06 -0700, Harriet
wrote:

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 [MVP]

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!

"John W. Vinson" wrote:

On Thu, 10 Sep 2009 14:44:06 -0700, Harriet
wrote:

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 [MVP]

On Fri, 11 Sep 2009 08:04:01 -0700, Harriet
wrote:

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 [MVP]

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 W. Vinson" wrote:

On Fri, 11 Sep 2009 08:04:01 -0700, Harriet
wrote:

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 [MVP]

specifically highlighting the less than sign after AND:

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

"John W. Vinson" wrote:

On Fri, 11 Sep 2009 08:04:01 -0700, Harriet
wrote:

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 [MVP]