Join Properties

[Love Buzz]

Hello all.

I have a problem and I think the answer is probably obvious. I have several
tables with the same exact fields (the column names are the same), but
different data in the fields (each table represents a months worth of data).

My goal is to create a series of queries that take all of the data into
account from the tables that I have joined. However, when I use 'Join
Properties' the query comes up empty.

I wouldn't think I would have to use SQL/Union since the column names are
the same, but maybe I am wrong.

Any assistance would be appreciated.

Thank you.

 

[John Spencer]

Your table structure means that you are going to have to use a UNION query.

Your other option is to build a new table with all the data in the current
tables plus a field to record the month (date of the data from each of the
multiple tables). Then append the data from each of the tables into the one
new table. This is the correct design for a table in a database.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Love Buzz]

Thanks John.

Here is what I did in SQL view for a Union Query:

SELECT
[Backroom Fee Refunds 1-2007].[Empl CC#], Count([Backroom Fee Refunds
1-2007].[User ID]) AS [CountOfUser ID]
FROM [Backroom Fee Refunds 1-2007] LEFT JOIN [Backroom Fee Reversals 2-2007]
ON [Backroom Fee Refunds 1-2007].[Empl CC#] = [Backroom Fee Reversals
2-2007].[Empl CC#]
GROUP BY [Backroom Fee Refunds 1-2007].[Empl CC#]
UNION ALL
SELECT
[Backroom Fee Reversals 2-2007].[Empl CC#], Count([Backroom Fee Reversals
2-2007].[User ID]) AS [CountOfUser ID]
FROM [Backroom Fee Refunds 1-2007] RIGHT JOIN [Backroom Fee Reversals 2-2007]
ON [Backroom Fee Refunds 1-2007].[Empl CC#] = [Backroom Fee Reversals
2-2007].[Empl CC#]
GROUP BY [Backroom Fee Reversals 2-2007].[Empl CC#]

The problem I am now having is that the 'CountOfUser ID' colum reflects both
months multiplied for each CC#. Any thoughts?

Thanks for your help.

 

[John W. Vinson]

Hello all.

I have a problem and I think the answer is probably obvious. I have several
tables with the same exact fields (the column names are the same), but
different data in the fields (each table represents a months worth of data).

Then you have a misdesigned database. You should have this data all in *ONE*
table, with a date field to distinguished data from various months.

My goal is to create a series of queries that take all of the data into
account from the tables that I have joined. However, when I use 'Join
Properties' the query comes up empty.

A Join joins records "side by side", taking records from two or more tables
and constructing one wider record. That's not what you want - you want to take
100 records from the May table and 125 records from the June table and get 225
records, joining "end to end"... right?

I wouldn't think I would have to use SQL/Union since the column names are
the same, but maybe I am wrong.

A UNION query is in fact the correct way to join two different tables end to
end. The column names could be the same or different. Just having two tables
with identical column names does *not* create any sort of connection between
those tables.

John W. Vinson [MVP]

 

[Love Buzz]

Thanks John.

You are exactly right. I was hoping that the Union Query would sum the
columns from the two tables. So just to clarify, I should just append the
data from each month into one table and the Union Query will not provide the
results I am looking for?

Thanks so much for your help.

 

[John W. Vinson]

Thanks John.

You are exactly right. I was hoping that the Union Query would sum the
columns from the two tables. So just to clarify, I should just append the
data from each month into one table and the Union Query will not provide the
results I am looking for?

Do you mean "sum" as in 2+3 = 5? Different issue.

If you want a *logical* append, then yes, a UNION query is exactly what you
want. The SQL would be

SELECT
[Backroom Fee Refunds 1-2007].[Empl CC#], [Backroom Fee Refunds
1-2007].[User ID]
FROM [Backroom Fee Refunds 1-2007]
UNION ALL
SELECT
[Backroom Fee Reversals 2-2007].[Empl CC#], [Backroom Fee Reversals
2-2007].[User ID]
FROM [Backroom Fee Refunds 1-2007]
UNION ALL
<the other ten months presumably>

This would give you one big "logical" table of all the data; you can save this
query and then either base a totals query on it, or (probably better) base an
Append query on it to populate your big single table.

John W. Vinson [MVP]