Combining rows in query

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

Guest

I have a table imported from Excel and I can't combine the data correctly.
The table looks like this (simplified):

CID SID Ret Gen Fee Ret Brand Fee Mail Gen Fee Mail
Brand Fee
11 77 $1.00
11 77 $2.00
11 77
$3.00
11 77
$4.00

When I run the select query, I can't make it combine all the data for CID
11, SID 77 into a single line like this:

CID SID Ret Gen Fee Ret Brand Fee Mail Gen Fee Mail
Brand Fee
11 77 $1.00 $2.00 $3.00
$4.00

Can someone help, please? Thanks.
 
create one extra column in your query like (just type in in the next empy
one):-

Com:[CID] & " " & [SID] etc.

Ludovic
 
Tracey

Don't!

Access is a relational database, not a "big spreadsheet". What you are
trying to do is use "repeating fields" (a spreadsheetly thing to do) when
what you need to do in Access is populate a table that looks something like:

FeeTable
CID
SID
FeeType (this would be your "Ret Gen Fee", "Ret Brand Fee", ...)
Amount

If you need to display the values in the format you described, use a
cross-tab query and possibly use that query as the source for a report.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks, Jeff. I understand your comments about the way the table is
structured, but unfortunately I am not entering the data - I am importing it
from a predefined Excel spreadsheet. I will be dealing with literally
thousands of lines of imported data each with 92 fields. I don't have enough
Access knowledge to understand how to make the table structure you suggest
when importing the Excel file. Is this something simple to do?
 
Tracey

Very simple.

Create a new table with a well-normalized structure (see my example as a
starting point).

Import the Excel data, but consider it "temporary".

Create a query (several queries) that take data from the temp table and
append it into your permanent table. One query for each "column" (i.e.,
FeeType)...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top