Query to create multipe total fields

  • Thread starter Thread starter Decembersonata
  • Start date Start date
D

Decembersonata

Ok, I have a fairly basic table. The table lists an ID number, a budget
number, and a dollar amount. Some ID numbers have multiple budget numbers
and dollar amounts associated with them. My task is to create a Make Table
query that takes all the records and only have one row for each unique ID
number. For instances where the ID number has multiple rows associated with
it in the original table, I need to create additional columns (i.e.
budgetnumber1, total1, budgetnumber2, total2, etc.) so that all the data for
each individual can be viewed in one row. What is the best way to go about
structuring this query?
 
I think a crosstab query will probably do what you need.

You did not give any sample data so it might work for you based on your data.
 
I did go the CrossTab route first, the only issue I had was the number of
records it was drawing back (about 1800) caused the crosstab to error.
 
I guess that you do not wish to post sample data.

What would the maximum quanity of budget numbers ever be?
 
Sorry, the sample data is payroll related, so that's why I'm hesitant to post
that. 3000 budgets is up towards the maximum number.
 
The maximum number of budgets would be something close to 3000. I'm not
posting sample data because the data is payroll related.
 
Sorry, the sample data is payroll related, so that's why I'm hesitant to post
that. 3000 budgets is up towards the maximum number.
Click to expand...

So do you really want a report that's 2756 columns wide...!? How will anyone
read it?
 
Ok, I have a fairly basic table. The table lists an ID number, a budget
number, and a dollar amount. Some ID numbers have multiple budget numbers
and dollar amounts associated with them. My task is to create a Make Table
query that takes all the records and only have one row for each unique ID
number. For instances where the ID number has multiple rows associated with
it in the original table, I need to create additional columns (i.e.
budgetnumber1, total1, budgetnumber2, total2, etc.) so that all the data for
each individual can be viewed in one row. What is the best way to go about
structuring this query?
Click to expand...

Let's step back a bit.

I doubt that you really want to print out a report with 6000 columns of budget
numbers and totals across the page!

What real-life business problem are you attempting to solve?

If you could create this enormously wide table (which you can't, not in Access
anyway since it's limited to 255 fields) what would you DO with it?

I'm certain that there is a different solution to your problem!
 
Back
Top