What am I doing wrong?

[Tcs]

I have two tables. Both contain transactions for a fiscal year. ( Jul-Jun ) The first table
contains multiple records, per location, per month. The second table contains one record, per
location, per month. ( The first has electric consumption & money, the second has demand electric &
money. ) I can't figure out how to join them ( assuming I need to join them at all ) in order to
get the records from both tables, using just one query, and sorted by year, month, and trans type.

Whatever I try, when I try to retrieve from both tables rather than just one, gets me four (4) times
( I think ) too many records.

Would anyone care to venture a guess? Any help would be appreciated. ( Who knows, perhaps tomorrow
morning, when I'm fresh, it'll be obvious. Right now? It ain't. )

Thanks in advance,

Tom

 

[Ken Snell \(MVP\)]

Tell us what data records you want the query to show you. Then we can tell
you how to write the query.

 

[Tcs]

Hmmm... Thought I had, but upon review, I guess I didn't.

I'd like to see the records from BOTH tables, sorted by:

period YY,
period MM,
Location ID,
Customer ID,
billing component.

I know I can query each table individually, and I guess that's what I'll be doing while I wait for a
response here. Both tables are identical, structurally. They only differ in what they contain.

Here's some sample data I pulled from each table.


Table 1 ( Consump )
------------------
UTCSID UTLCID UTSVC UTRBCM UTCHGT UTTTYP UTTDSC UTTAMT UTRJUR UTRCLS UTRMSZ UTPEMM
UTPEYY UTCHCN UTDMCN Timestamp
39687 256 EL BLC2 200-400 HOURS 267.26 CA 7 6 8352 0
7/17/2007 8:49:12 AM
39687 256 EL CDBS BASE CHARGE ELECTRIC 25 CA 7 6 0 0 7/17/2007
8:49:12 AM
39687 256 EL ENR1 0-200 HOURS 1086.34 CA 7 6 11808 0
7/17/2007 8:49:12 AM
39687 256 EL FA FUEL ADJUSTMENT 29.43 CA 7 6 20160 0
7/17/2007 8:49:12 AM
97491 258 EL CDBS BASE CHARGE ELECTRIC 25 CA 7 6 0 0 7/17/2007
8:49:12 AM
97491 258 EL ENR1 0-200 HOURS 29.44 CA 7 6 320 0
7/17/2007 8:49:12 AM
97491 258 EL FA FUEL ADJUSTMENT 0.47 CA 7 6 320 0
7/17/2007 8:49:12 AM


Table 2 ( Demand )
------------------
UTCSID UTLCID UTSVC UTRBCM UTCHGT UTTTYP UTTDSC UTTAMT UTRJUR UTRCLS UTRMSZ UTPEMM
UTPEYY UTCHCN UTDMCN Timestamp
39687 256 EL DMD1 DEMAND CHARGE 132.84 CA 7 6 0 59.04
7/17/2007 1:49:33 PM
97491 258 EL DMD1 DEMAND CHARGE 11.25 CA 7 6 0 5
7/17/2007 1:49:33 PM
53493 7342 EL DMD1 DEMAND CHARGE 256.5 CA 12 6 0 114
7/17/2007 1:49:33 PM
40347 704 EL DMD1 DEMAND CHARGE 46.8 CA 7 6 0 20.8
7/17/2007 1:49:33 PM


UTCSID = Customer ID
UTLCID = Location ID
UTRBCM = Billing Component


Thanks a lot, I appreciate it.

 

[John Spencer]

Perhaps what you are looking for is a UNION query.

SELECT <<LIST OF FIELDS>>
FROM Table1
UNION ALL
SELECT <<List of fields in same order>>
FROM Table2
ORDER BY [Period YY], [Period MM], [Location ID], [Customer ID], [Billing
Component]

You cannot build this query using the query grid, but must do it in the SQL
view. However, you can build the query for the first table in the grid and
then change the view (View: SQL) to the SQL window. The following outline
of the process will work IF the field names are all identical between the
two tables; othewise you will have to do some typing

--Copy the SELECT and FROM lines
-- Remove the semi-colon
-- Enter
UNION ALL before the ORDER BY clause (if it exists)
-- Paste the lines you copied immediately after the union all
-- Edit the table name to the name of the 2nd table.


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