I'm a crosstab dummy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!!!
 
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
 
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!
 
Back
Top