Rollup multiple records into one record

  • Thread starter Thread starter kidkosmo
  • Start date Start date
K

kidkosmo

Hi, Gang,

I'm having a dillema that I probably should be able to figure out on
my own, but I'm having issues.

I have multiple records which are laid out such as this:

Acct ProductName
123 AB1
123 BA1
123 CD1

I want to roll those multiple records into one record that might look
something like this:

Acct AB1 BA1 CD1
123 Yes Yes Yes

For some reason, this is escaping me.

Thanks!
 
kidkosmo said:
Hi, Gang,

I'm having a dillema that I probably should be able to figure out on
my own, but I'm having issues.

I have multiple records which are laid out such as this:

Acct ProductName
123 AB1
123 BA1
123 CD1

I want to roll those multiple records into one record that might look
something like this:

Acct AB1 BA1 CD1
123 Yes Yes Yes

For some reason, this is escaping me.
Click to expand...

As well it should. You are storing the information correctly. If you need to
view it like:

Acct AB1 BA1 CD1
123 Yes Yes Yes

use a crosstab query.
 
Create a crosstab query where:
- Acct is a Row Heading
- ProductName is a Column Heading
- ProductName is the Value (choosing Count as Total.)
 
As others have already said, create a crosstab query. Here is a tutorial on
how to do this:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html

You can have one Value, which you've currently indicated as "Yes". You may
have to create an expression in the query in order to generate a "Yes" or
"No" value. Other possibilities include a count of orders, or a sum of the
dollar value of orders placed.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
 
Back
Top