I'm a crosstab dummy

B

Bonnie

Hi there. Using A02 on XP. Have never had a loving relationship with crosstab
queries. I try, goodness knows, I try. I know this is so simple most folks
reading this will need to contain themselves but I need some advice.

I have a simple Excel spreadsheet that I'm importing and I need to export a
CSV file when data has been manipulated, etc. The spreadsheet/table has just
6 fields:

F1=SSN
F2=LName
F3=FName
F4=EeDefAmt
F5=ErDefAmt
F6=LoanAmt

My problem is this: data being imported has multiple records for most
employees. I need to append one record per person to an export table.
Selecting a field for Row or Column headings confuses the bejeebers out of
me. I've tried and tried and just get frustrated. Help is no help because I'm
missing something.

If I have this:

111111111 Doe Jane 25.00 15.50 0
111111111 Doe Jane 56.50 18.25 4.50
555555555 Down Dirk 55.25 0 0
888888888 Ford Fred 0 26.50 16.00
888888888 Ford Fred 85.00 55.00 25.00

I need to end up with this:

111111111 Doe Jane 81.50 33.75 4.50
555555555 Down Dirk 55.25 0 0
888888888 Ford Fred 85.00 81.50 41.00

I know I can get the results on a report with grouping levels but I need it
at the query level so I can run an append.

Thanks in advance for any help or advice!!!
 
D

Duane Hookom

You don't need a crosstab, just a simple totals query.
SELECT SSN, LNAME, FNAME, Sum(EeDefAmt) as SumEeDef, Sum(ErDefAmt) as
SumErDef, Sum(LoanAmt) as SumLoan
FROM tblA
GROUP BY SSN, LNAME, FNAME
 
B

Bonnie

Duane, today you are my HERO! Thank you VERY much for the advice, I did end
up having to import my fields as F1, F2, F3, etc. so with the switch, it
works GREAT! I knew I was missing something easy.

Thank you, thank you, thank you. Have a wonderful weekend!
 
Top