Query to create multipe total fields

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?

0
Utf
1/20/2010 11:37:01 PM
access.queries 6343 articles. 1 followers. Follow

7 Replies
675 Views

Similar Articles

[PageSpeed] 37

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.

-- 
Build a little, test a little.


"Decembersonata" wrote:

> 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?
> 
0
Utf
1/20/2010 11:47:01 PM
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.  

"KARL DEWEY" wrote:

> 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.
> 
> -- 
> Build a little, test a little.
> 
> 
> "Decembersonata" wrote:
> 
> > 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?
> > 
0
Utf
1/20/2010 11:51:01 PM
I guess that you do not wish to post sample data.

What would the maximum quanity of budget numbers ever be?

-- 
Build a little, test a little.


"Decembersonata" wrote:

> 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.  
> 
> "KARL DEWEY" wrote:
> 
> > 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.
> > 
> > -- 
> > Build a little, test a little.
> > 
> > 
> > "Decembersonata" wrote:
> > 
> > > 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?
> > > 
0
Utf
1/21/2010 12:14:01 AM
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.

"KARL DEWEY" wrote:

> I guess that you do not wish to post sample data.
> 
> What would the maximum quanity of budget numbers ever be?
> 
> -- 
> Build a little, test a little.
> 
> 
> "Decembersonata" wrote:
> 
> > 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.  
> > 
> > "KARL DEWEY" wrote:
> > 
> > > 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.
> > > 
> > > -- 
> > > Build a little, test a little.
> > > 
> > > 
> > > "Decembersonata" wrote:
> > > 
> > > > 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?
> > > > 
0
Utf
1/21/2010 4:08:01 PM
The maximum number of budgets would be something close to 3000.  I'm not 
posting sample data because the data is payroll related.

"KARL DEWEY" wrote:

> I guess that you do not wish to post sample data.
> 
> What would the maximum quanity of budget numbers ever be?
> 
> -- 
> Build a little, test a little.
> 
> 
> "Decembersonata" wrote:
> 
> > 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.  
> > 
> > "KARL DEWEY" wrote:
> > 
> > > 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.
> > > 
> > > -- 
> > > Build a little, test a little.
> > > 
> > > 
> > > "Decembersonata" wrote:
> > > 
> > > > 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?
> > > > 
0
Utf
1/21/2010 4:27:02 PM
On Thu, 21 Jan 2010 08:08:01 -0800, Decembersonata
<Decembersonata@discussions.microsoft.com> wrote:

>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.

So do you really want a report that's 2756 columns wide...!? How will anyone
read it?

-- 

             John W. Vinson [MVP]
0
John
1/21/2010 5:30:26 PM
On Wed, 20 Jan 2010 15:37:01 -0800, Decembersonata
<Decembersonata@discussions.microsoft.com> wrote:

>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?

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!

-- 

             John W. Vinson [MVP]
0
John
1/21/2010 6:44:29 PM
Reply:

Similar Artilces:

Timer for Queries?
Does anyone know of a macro or add-on that I could use as a simple timer for queries? I don't like to use the CPU time in the Task Manager because of it's inaccuracy when not receiving full CPU usage. THanks for any suggestions -- cmungs Exactly what are you trying to accomplish? Are you trying to cause a query to run automatically every so often? If so, you will need to use the timer event on a form to do that. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "cmungs" <cmungs@discussions.microsoft.com> wrote in message news:88EC7019-045F-4EF...

Erratic results from query criteria
I am getting different results from running the same query with the same selections. One moment it is all behaving as expected, the next it has gone haywire. (I have done what appears to me to be EXACTLY what I have done in another database, where it works perfectly every single time.) In a query I have, amongst others, the following fields: Category SubCategory Company I want to be able to select any OR ALL of the relevant fields. I have the following criteria: Like "*"&[Type Category otherwise leave blank for ALL]&"*" Like "*"&a...

How do I create a B&B reservation system in Excel?
Trying to create a reservation system for 6 room B&B in excel. Does anyone have template or know where to get one? ...

Multiplying Columns in total
How would one go about multiplying Column A in sheet1 with Column A i sheet2 to make a new column in sheet3 -- Yavaro ----------------------------------------------------------------------- Yavarow's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1608 View this thread: http://www.excelforum.com/showthread.php?threadid=27534 =Sheet1!A1*Sheet2!A1 "Yavarow" <Yavarow.1f7jxc@excelforum-nospam.com> wrote in message news:Yavarow.1f7jxc@excelforum-nospam.com... > > How would one go about multiplying Column A in sheet1 with Column A in > sheet...

How do I create a grading system in Office Excel?
Many teachers use computers to build their grading policy and generate the grades that their students get for each semester. Can I do this in Excel? If so, how is this done? Miss Dunkley, this should get you started, http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=grading -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Miss Dunkley" <Miss Dunkley@discussions.microsoft.com> wrote in message news:7...

How to create odd graph
I have a spreadsheet that has a number of values entered each day. I would like to create a bar chart that will take the values for the corresponding day for the past month or so and graph the average. So, there would be seven of these charts. Each has a 0-1AM, 1-2AM, and so on. The value for each of those time periods will be the average of the values for that day of the week over the past month. Mondays chart would have the past 4 Mondays, etc. Each time a new day is added to the spreadsheet, the chart for that day of the week should "jump" forward, and use that days data whi...

Update Query ?
Is there a way to 1) Assign events when adding in data thru an update query for example if you append a bunch of data into a table could you then do some kind of an update query to say if there is an initial event and no others tag this one Renewal 1 etc.. the only real data you have to go on would be Customer A and the total amt .. the event, renewal event and the delta would all need to be added in example: Say Customer A comes in with an initial deal then Customer A renews their deal So, the data would look like when you're done.: Year Custo...

create dataset from stored procedure
I have a tsql stored procedure I want to drag from server explorer onto an xsd file. I get the error ' xml schema could not be interpreted'. The stored procedure does a select from a udf, that's it. "vickilynnpgc" <vickilynnpgc@discussions.microsoft.com> wrote in message news:3A7D31B8-0170-4C19-91E6-C02E0D3B1EC7@microsoft.com... >I have a tsql stored procedure I want to drag from server explorer onto an > xsd file. I get the error ' xml schema could not be interpreted'. The > stored > procedure does a select from a udf, that's it. ...

How to create a cutomized view in outlook
hi all , i would like to create a view in the INBOX let say for exampl if 6 April 2004 , i would like to have all my email received wil categorized all in this date . i have tried to created a customise view for my inbox , but this inbox keep giving me the view let say fo example (View 1) 6 April 2004 11:00 AM - SmilePop Easter bunny 06 April 2004 11:00 AM 6 April 2004 12:00 PM - Linda Teo 06 April 2004 12:00 PM i have a link that i have created a view for the inbox for the View 1 This is the link http://www.asp1re.com/outlook.jpg I would want my view to be in this way -----------------...

field highlight
i have an account spreadsheet. with a column heading reading status. How can i tell excel to highlight all cells containing status "I" fo inactive in red, and status "A" for active in green -- Message posted from http://www.ExcelForum.com You would use Format>Conditional Formatting for this. Select the column in question(I'll assume A). Format>Conditional Formatting>Formula is: enter =A1="I" Format>Pattern Red>OK Add =A1="A" Format>Pattern Green>OK Note: you can use up to three conditional formats(four if you count...

design query match anywhere?
In the design query I want to match the letters "ABC" anywhere in the column 'description'. I could not get instr to work by using: InStr(description, "ABC")) > 0 How else can I match ABC (upper and lowercase) to anywhere in my 'description' column? Thanks! Are you saying you put that InStr bit as a criteria under your Description field in the query designer? Try putting Like "*ABC*" as the criteria instead. Alternatively, you could add a computed field to the query InStr([Description, "ABC") and then put >0 as the ...

Unexpected Error- Running Advance Find Query
I am getting a message that says "Unexpected Error An Error has occurred" when I am running an advanced find query on two custom entities. It seems to be when I have certain custom fields included in my view that the query errors out. Any help on this would be greatly appreciated. Thanks so much. Just wanted to add that I seem to get errors when there is a 0.00 amount in these fields... "TJ" wrote: > I am getting a message that says "Unexpected Error An Error has occurred" > when I am running an advanced find query on two custom entities. It se...

It it possible to create a database in Access for this:
Hi, Im trying to create a database, but not having much luck. Im new to MS Access. I would like to use a switchboard that people open - it will be used by at least 6 people - so when they open it they have 3 or 4 options to choose from - nice a simple - not confusing for anyone on where to go or what to do. On that i was going to add a form to fill in details on clients. Then from the information that is added in that goes to a table. Such as Name, Number, DOB, location will be filled out for each client. Under that - 10 drop down menus to click on different jobs. Each job goes into a sep...

Inventory trans created through RMA missing serials
We are having random issues with inventory transactions created through RMA generating without the serial information. The inventory transaction then does not post through. The Edit List shows error: The serial numbers and extended quantity for this item do not match. The item is listed on the transaction, and all the other information (customer, site, etc) appears to be coming through. The RMA does have the serial number in all instances. This does not happen on all inventory transactions; appears to be random. Not tied to RMA type, site, item, customer, etc. Does not appear to be any pat...

Removing a field From a Pivot Table
I'm working with a pvt table in excel. Right now there are several fields in the pvt table, including the month different entries are made and the account that made them. When i hit the show detail button in the pivot table i get the detail all of the entries grouped by month and by account on a separate sheet. I want to continue to do this, but i want to also be able to get a second view where i hit the show detail button and only see the detail grouped my account (month is either hidden or totally removed from this list of data in the pvt table and the rows that composed the different...

pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...

Creating a transparent picture control
Hi, I have added a picture control to my dialog and set its type to bitmap. I then imported a bitmap to my resouce and set my picture contol to display this image. I'd like this image to be transparent, so that the white background does not diplay. How do I do this? Thanks for your help, Barry. bg_ie@yahoo.com skrev: > Hi, > > I have added a picture control to my dialog and set its type to bitmap. > I then imported a bitmap to my resouce and set my picture contol to > display this image. I'd like this image to be transparent, so that the > white background does...

a count field in a query?
Hello, I have a query in which I would like to create a field which increments by 1 for each record selected, so if there are 10 records selected by the query, this column would show numbers 1 to 10. Ideally I would like to have some text in front of each number, say invoice1, invoice2 etc. From a previous posting I realise that this is frowned upon but it would be the simplest solution for my problem! How can I do this? Thanks Geoff On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote: >Hello, > >I have a query in which I would like to create a field which >...

Using categories, task, etc to create reports
I would like to summarise/quantify information from Outlook, using existing fields. For example: the # of tasks and phone calls that belong to a particular Category; or, the amount of time spent for a category or for a contact, using date ranges. Is there anything existing in Outlook? is there a way to do this in Outlook? Any suggestions? ...

Create and populate a SmartArt graphic
Hi, I'm trying to create an organizational chart on the fly in VBA. So what I'd like to do is add a SmartArt Hierarchy graphic to the worksheet, and then add names into it. I've tried adding a chart and then looking through its components in the 'watch' window as well as searching around online, but I haven't made any progress in creating or editing it from VBA. Any ideas? Thank you! Nathan Any ideas on where to start? Or is this impossible? ...

Tracking Email in CRM and Creating of Contact
CRM 3, OUtlook 2007 & v3c When I press "Track in CRM" an email from a contact that is not in CRM it adds it to CRM but does not notify me that the contact (email) is not in CRM. I have to then go back and open it in CRM to check if the contact exists or has been matched to something. Is this correct behaviour? We have previously been using Salesforce.com and it will prompt you to add or join to a contact in that situation. This is by design, it would be very disruptive to prompt the user each time this occurs. There are a couple of workaround for you. 1) Use the CRM Addr...

why does Word display my field codes upon opening?
I am using Office 2007, and version 11 of Reference Manager. When I open my document, both the Reference Manager fields and the page numbers display with the cryptic words in [brakets]. I must Select All and 'toggle field codes' every time. Can I have Word open with the fields already 'toggled'? Office Button>Word Options>Advanced then uncheck the "Show field codes instead of their values" in the Show document content section of the Word Options dialog. -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unle...

Saving Username and Pass with Web Queries
I am pulling data from a website that requires you to provide a usernam and password everytime you visit the site. Excel has cachin capabilities, but everytime you close excel and try and open up th spreadsheet again, it can't login. What is the fix for this? Thanks, Hend -- Message posted from http://www.ExcelForum.com Bump! --- Message posted from http://www.ExcelForum.com/ ...

Location of Grand Total in Pivot table
Is the a way to show the Grand total next to the selected Field as opposed to the right now column ( I am using a file that had data results by Date)? You can not move the grand totals. They is where they is and that's where they stays... -- HTH... Jim Thomlinson "Rosemary" wrote: > Is the a way to show the Grand total next to the selected Field as opposed to > the right now column ( I am using a file that had data results by Date)? ...

Create a pdf with web links from file created in excel
How do I preserve web links I created in an excel spreadsheet when I convert to a pdf? ...