If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Calculate a percentage using 2 different query totals
I have setup 2 separate queries to get specific totals. It is impossible to
combine them into 1, as they both use 1 key field with different criteria to get the correct total. Is there any way to calculate and display, in a query or report, a percentage as it relates to those 2 totals. Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32% All I Need is the percentage to show with the appropriate record, not the calculation. Please Help, jbeck2010 |
#2
|
|||
|
|||
If each query returns only one record, you can combine them in another
query. SELECT query1.*, query2.* FROM query1, query2; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have setup 2 separate queries to get specific totals. It is impossible to combine them into 1, as they both use 1 key field with different criteria to get the correct total. Is there any way to calculate and display, in a query or report, a percentage as it relates to those 2 totals. Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32% All I Need is the percentage to show with the appropriate record, not the calculation. Please Help, jbeck2010 |
#3
|
|||
|
|||
Duane,
It was great to hear from you and thanks again for your help earlier this month. I don't quite understand what you meant by SELECT query1.*, query2.* FROM query1, query2; I assume it is code or an expression to be written some place or is it a proceedure? Let me try to be a little more specific about what I am trying to do. The 2 queries are setup as follows; 1. Customer ID, Customer Name, Product, Product Type=Total Individual Purchased Sum Not Canceled and Not Void 2. Product, Product Type=Total Product Sold to Everyone Sum Sold *What I am trying to do is show the percentage one Cusomer has purchased in relationship to the total Product sold. Duane, I would like to thank you in advance for your time and expert advise in solving my dilemma. Best Regards, jbeck2010 "Duane Hookom" wrote: If each query returns only one record, you can combine them in another query. SELECT query1.*, query2.* FROM query1, query2; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have setup 2 separate queries to get specific totals. It is impossible to combine them into 1, as they both use 1 key field with different criteria to get the correct total. Is there any way to calculate and display, in a query or report, a percentage as it relates to those 2 totals. Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32% All I Need is the percentage to show with the appropriate record, not the calculation. Please Help, jbeck2010 |
#4
|
|||
|
|||
Can't you create two totals queries where the ProductID is unique in each
query. One query contains only orders from a single customer while the other contains orders from all customers? Then join the two queries together by the ProductID? -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, It was great to hear from you and thanks again for your help earlier this month. I don't quite understand what you meant by SELECT query1.*, query2.* FROM query1, query2; I assume it is code or an expression to be written some place or is it a proceedure? Let me try to be a little more specific about what I am trying to do. The 2 queries are setup as follows; 1. Customer ID, Customer Name, Product, Product Type=Total Individual Purchased Sum Not Canceled and Not Void 2. Product, Product Type=Total Product Sold to Everyone Sum Sold *What I am trying to do is show the percentage one Cusomer has purchased in relationship to the total Product sold. Duane, I would like to thank you in advance for your time and expert advise in solving my dilemma. Best Regards, jbeck2010 "Duane Hookom" wrote: If each query returns only one record, you can combine them in another query. SELECT query1.*, query2.* FROM query1, query2; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have setup 2 separate queries to get specific totals. It is impossible to combine them into 1, as they both use 1 key field with different criteria to get the correct total. Is there any way to calculate and display, in a query or report, a percentage as it relates to those 2 totals. Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32% All I Need is the percentage to show with the appropriate record, not the calculation. Please Help, jbeck2010 |
#5
|
|||
|
|||
Duane,
Thank you for your helpful and timely response to my question. I'm trying to think through your suggestion and see if your idea about a unique ProductID will work. For the moment, my problem is that I don't know how to assign a unique ProductID to the 2 different queries. There is only 1 Product, but 3 different statuses of that Product. (Active, Canceled or Void) The database that I have designed is basically very simple. It has 2 main tables; 1. Customer Personal Information (A very large table) 2. Product Puchase Information (Product,Type,Date etc.) They are joined in a one to many relationship using the customerID as the Primary Key. The Product purchase information is the many side. All of the data input is done on 2 pages of 1 single form. All of the queries,forms and reports are based on those 2 tables. Reports are automatically updated and self generated as the information is input on those 2 pages of that 1 single form. All I have to do is Print them. Duane, after thinking through the above information that I have just written you maybe the answer is to create 2 other tables with a unique ProductID as you suggested and link them to the main tables somehow. 1. Total Product sold to one individual 2. Total Product sold to everyone Do you think I'm on the right track or do you have another suggestion. Thanks again for sharing your expertise to an "Old Dog" that tryin' to learn some new tricks. God Bless and have a Great Day, jbeck2010 "Duane Hookom" wrote: Can't you create two totals queries where the ProductID is unique in each query. One query contains only orders from a single customer while the other contains orders from all customers? Then join the two queries together by the ProductID? -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, It was great to hear from you and thanks again for your help earlier this month. I don't quite understand what you meant by SELECT query1.*, query2.* FROM query1, query2; I assume it is code or an expression to be written some place or is it a proceedure? Let me try to be a little more specific about what I am trying to do. The 2 queries are setup as follows; 1. Customer ID, Customer Name, Product, Product Type=Total Individual Purchased Sum Not Canceled and Not Void 2. Product, Product Type=Total Product Sold to Everyone Sum Sold *What I am trying to do is show the percentage one Cusomer has purchased in relationship to the total Product sold. Duane, I would like to thank you in advance for your time and expert advise in solving my dilemma. Best Regards, jbeck2010 "Duane Hookom" wrote: If each query returns only one record, you can combine them in another query. SELECT query1.*, query2.* FROM query1, query2; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have setup 2 separate queries to get specific totals. It is impossible to combine them into 1, as they both use 1 key field with different criteria to get the correct total. Is there any way to calculate and display, in a query or report, a percentage as it relates to those 2 totals. Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32% All I Need is the percentage to show with the appropriate record, not the calculation. Please Help, jbeck2010 |
#6
|
|||
|
|||
I don't know why you need to create two new tables where you can create a
totals/group by query to derive the recordset. Maybe you need to provide your table structures, a few sample records, and what you expect for results. -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, Thank you for your helpful and timely response to my question. I'm trying to think through your suggestion and see if your idea about a unique ProductID will work. For the moment, my problem is that I don't know how to assign a unique ProductID to the 2 different queries. There is only 1 Product, but 3 different statuses of that Product. (Active, Canceled or Void) The database that I have designed is basically very simple. It has 2 main tables; 1. Customer Personal Information (A very large table) 2. Product Puchase Information (Product,Type,Date etc.) They are joined in a one to many relationship using the customerID as the Primary Key. The Product purchase information is the many side. All of the data input is done on 2 pages of 1 single form. All of the queries,forms and reports are based on those 2 tables. Reports are automatically updated and self generated as the information is input on those 2 pages of that 1 single form. All I have to do is Print them. Duane, after thinking through the above information that I have just written you maybe the answer is to create 2 other tables with a unique ProductID as you suggested and link them to the main tables somehow. 1. Total Product sold to one individual 2. Total Product sold to everyone Do you think I'm on the right track or do you have another suggestion. Thanks again for sharing your expertise to an "Old Dog" that tryin' to learn some new tricks. God Bless and have a Great Day, jbeck2010 "Duane Hookom" wrote: Can't you create two totals queries where the ProductID is unique in each query. One query contains only orders from a single customer while the other contains orders from all customers? Then join the two queries together by the ProductID? -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, It was great to hear from you and thanks again for your help earlier this month. I don't quite understand what you meant by SELECT query1.*, query2.* FROM query1, query2; I assume it is code or an expression to be written some place or is it a proceedure? Let me try to be a little more specific about what I am trying to do. The 2 queries are setup as follows; 1. Customer ID, Customer Name, Product, Product Type=Total Individual Purchased Sum Not Canceled and Not Void 2. Product, Product Type=Total Product Sold to Everyone Sum Sold *What I am trying to do is show the percentage one Cusomer has purchased in relationship to the total Product sold. Duane, I would like to thank you in advance for your time and expert advise in solving my dilemma. Best Regards, jbeck2010 "Duane Hookom" wrote: If each query returns only one record, you can combine them in another query. SELECT query1.*, query2.* FROM query1, query2; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have setup 2 separate queries to get specific totals. It is impossible to combine them into 1, as they both use 1 key field with different criteria to get the correct total. Is there any way to calculate and display, in a query or report, a percentage as it relates to those 2 totals. Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32% All I Need is the percentage to show with the appropriate record, not the calculation. Please Help, jbeck2010 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Calculate a Percentage based off one record | G. Wolfe | Setting Up & Running Reports | 2 | December 28th, 2004 09:51 PM |
Financial Comparison | Steven Cheng | Setting Up & Running Reports | 13 | November 26th, 2004 10:59 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |