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 |
#11
|
|||
|
|||
Duane,
Thank you for your response. I followed your instructions to setup the query, however, when I click to run the query a popup box appears and asks me to enter a parameter value for the customer ID. When I put in a number, the query will run but the result is either a -1 or 0. The other thing is that only a Percent field shows in the query and not the total amount purchased by one individual. Duane, I know this is probably trying your patients, but do you have any other thoughts. Thanks for your kindness jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#12
|
|||
|
|||
Duane,
Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#13
|
|||
|
|||
I you get prompted for Customer ID then you don't have a field in your
table(s) named Customer ID. That's why I suggested you make sure my SQL matched your table and field names. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank you for your response. I followed your instructions to setup the query, however, when I click to run the query a popup box appears and asks me to enter a parameter value for the customer ID. When I put in a number, the query will run but the result is either a -1 or 0. The other thing is that only a Percent field shows in the query and not the total amount purchased by one individual. Duane, I know this is probably trying your patients, but do you have any other thoughts. Thanks for your kindness jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#14
|
|||
|
|||
If you have two queries where one returns only one record, you can create
another query with both queries as the source and then use fields from both source queries. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#15
|
|||
|
|||
Duane,
On the 1 issue with the CustomerID, there is a space between Customer and ID in the table. When I type it as it is in the table it gives a syntax error message about the SQL statement and when I type it as CustomerID it gives the result I mentioned before. It doesn't work for me somehow. I must be doing something else wrong. In Regards to the 2 queries, please clarify what you mean by (one record). Query 1 has 4 fields; Customer ID, Name, "Amount Purchased", Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by an Individual from many records. Query 2 has 2 fields; Amount Purchased, Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by everyone from many records. Are you saying, if the queries are pulling information from more than one record you cannot combine the two queries or that if the query produces one total as the result of the query, it is one record, and you can combine the one total result fom each query in another query. Duane, I am very appreciative of your patients with me on this subject and understand if you think I should seek other help. It seems as though this should be very simple, but for some reason I'm just not getting it. Thanks for all your efforts on my behalf, jbeck2010 "Duane Hookom" wrote: If you have two queries where one returns only one record, you can create another query with both queries as the source and then use fields from both source queries. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#16
|
|||
|
|||
jbeck2010,
You have to understand basic syntax and the difference between field names with or without spaces. These two are not the same "Customer ID" and "CustomerID". More experienced programmers never create field, table, or object names with spaces. Those that use spaces must place []s around the field names like [Customer ID]. By (returns one record) means that when you display the results of the query (you stated you had two that worked), you only see one record. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, On the 1 issue with the CustomerID, there is a space between Customer and ID in the table. When I type it as it is in the table it gives a syntax error message about the SQL statement and when I type it as CustomerID it gives the result I mentioned before. It doesn't work for me somehow. I must be doing something else wrong. In Regards to the 2 queries, please clarify what you mean by (one record). Query 1 has 4 fields; Customer ID, Name, "Amount Purchased", Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by an Individual from many records. Query 2 has 2 fields; Amount Purchased, Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by everyone from many records. Are you saying, if the queries are pulling information from more than one record you cannot combine the two queries or that if the query produces one total as the result of the query, it is one record, and you can combine the one total result fom each query in another query. Duane, I am very appreciative of your patients with me on this subject and understand if you think I should seek other help. It seems as though this should be very simple, but for some reason I'm just not getting it. Thanks for all your efforts on my behalf, jbeck2010 "Duane Hookom" wrote: If you have two queries where one returns only one record, you can create another query with both queries as the source and then use fields from both source queries. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#17
|
|||
|
|||
Duane,
Thank you so much for your last Response. IT GAVE ME THE ANSWER TO MY QUESTION. "How do you combine 2 Queries into 1 if they both use 2 of the same fields to get a different result". (The Totals) The answer is very simple. It's not about adding a table in design view or writing the proper code in the SQL statement. It is not about using the Query Wizard because you will get and error message saying something like; you are trying to add two queries from incompatible record sources. When you explained the meaning of returning ONE RECORD and that you "COULD" combine the 2 Queries, the solution was obvious. It was something I had not tried and it was so simple it was stupid on my part. THE ANSWER IS...for those who want to know 1. Click on the Queries Tab on the left side of the Database Window 2. Click New 3. Select Design View 4. Click on the Queries tab on show table box 5. Double Click on the 2 queries that you want to add 6. Double Click on the fields to add them to the design grid 7. Click Run and "It works!" From there it was very easy to get the Percentage I spoke of Originally. 8. Right Click in the Blank Field to the right of the last field in the Query 9. Click "ZOOM" 10. Enter the appropriate expression from your field headings in the Query (The Totals) Like: Percentage: [ ] / [ ] 11. Click Run and "IT'S DONE". You might also want to right click on the Percentage field in the design grid and click properties to set the caption and format of the numbers. Well, Duane, this little exercise has been quite and experience. I want to thank you for not giving up on me. It is the mark of a great teacher to make a student think, and you sure did your job on this one. Let me know how I did... Best Regards, jbeck2010 "Duane Hookom" wrote: jbeck2010, You have to understand basic syntax and the difference between field names with or without spaces. These two are not the same "Customer ID" and "CustomerID". More experienced programmers never create field, table, or object names with spaces. Those that use spaces must place []s around the field names like [Customer ID]. By (returns one record) means that when you display the results of the query (you stated you had two that worked), you only see one record. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, On the 1 issue with the CustomerID, there is a space between Customer and ID in the table. When I type it as it is in the table it gives a syntax error message about the SQL statement and when I type it as CustomerID it gives the result I mentioned before. It doesn't work for me somehow. I must be doing something else wrong. In Regards to the 2 queries, please clarify what you mean by (one record). Query 1 has 4 fields; Customer ID, Name, "Amount Purchased", Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by an Individual from many records. Query 2 has 2 fields; Amount Purchased, Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by everyone from many records. Are you saying, if the queries are pulling information from more than one record you cannot combine the two queries or that if the query produces one total as the result of the query, it is one record, and you can combine the one total result fom each query in another query. Duane, I am very appreciative of your patients with me on this subject and understand if you think I should seek other help. It seems as though this should be very simple, but for some reason I'm just not getting it. Thanks for all your efforts on my behalf, jbeck2010 "Duane Hookom" wrote: If you have two queries where one returns only one record, you can create another query with both queries as the source and then use fields from both source queries. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#18
|
|||
|
|||
Glad to hear you experienced success!
One point about formatting your results. I almost always leave the display formatting to the control on my form or report. Queries and tables retrieve records and forms or reports format the records. -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, Thank you so much for your last Response. IT GAVE ME THE ANSWER TO MY QUESTION. "How do you combine 2 Queries into 1 if they both use 2 of the same fields to get a different result". (The Totals) The answer is very simple. It's not about adding a table in design view or writing the proper code in the SQL statement. It is not about using the Query Wizard because you will get and error message saying something like; you are trying to add two queries from incompatible record sources. When you explained the meaning of returning ONE RECORD and that you "COULD" combine the 2 Queries, the solution was obvious. It was something I had not tried and it was so simple it was stupid on my part. THE ANSWER IS...for those who want to know 1. Click on the Queries Tab on the left side of the Database Window 2. Click New 3. Select Design View 4. Click on the Queries tab on show table box 5. Double Click on the 2 queries that you want to add 6. Double Click on the fields to add them to the design grid 7. Click Run and "It works!" From there it was very easy to get the Percentage I spoke of Originally. 8. Right Click in the Blank Field to the right of the last field in the Query 9. Click "ZOOM" 10. Enter the appropriate expression from your field headings in the Query (The Totals) Like: Percentage: [ ] / [ ] 11. Click Run and "IT'S DONE". You might also want to right click on the Percentage field in the design grid and click properties to set the caption and format of the numbers. Well, Duane, this little exercise has been quite and experience. I want to thank you for not giving up on me. It is the mark of a great teacher to make a student think, and you sure did your job on this one. Let me know how I did... Best Regards, jbeck2010 "Duane Hookom" wrote: jbeck2010, You have to understand basic syntax and the difference between field names with or without spaces. These two are not the same "Customer ID" and "CustomerID". More experienced programmers never create field, table, or object names with spaces. Those that use spaces must place []s around the field names like [Customer ID]. By (returns one record) means that when you display the results of the query (you stated you had two that worked), you only see one record. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, On the 1 issue with the CustomerID, there is a space between Customer and ID in the table. When I type it as it is in the table it gives a syntax error message about the SQL statement and when I type it as CustomerID it gives the result I mentioned before. It doesn't work for me somehow. I must be doing something else wrong. In Regards to the 2 queries, please clarify what you mean by (one record). Query 1 has 4 fields; Customer ID, Name, "Amount Purchased", Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by an Individual from many records. Query 2 has 2 fields; Amount Purchased, Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by everyone from many records. Are you saying, if the queries are pulling information from more than one record you cannot combine the two queries or that if the query produces one total as the result of the query, it is one record, and you can combine the one total result fom each query in another query. Duane, I am very appreciative of your patients with me on this subject and understand if you think I should seek other help. It seems as though this should be very simple, but for some reason I'm just not getting it. Thanks for all your efforts on my behalf, jbeck2010 "Duane Hookom" wrote: If you have two queries where one returns only one record, you can create another query with both queries as the source and then use fields from both source queries. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 |
#19
|
|||
|
|||
Duane,
Thanks for the "Tip". I won't forget. I'll make my teacher proud. Have a Blessed Day, jbeck2010 "Duane Hookom" wrote: Glad to hear you experienced success! One point about formatting your results. I almost always leave the display formatting to the control on my form or report. Queries and tables retrieve records and forms or reports format the records. -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, Thank you so much for your last Response. IT GAVE ME THE ANSWER TO MY QUESTION. "How do you combine 2 Queries into 1 if they both use 2 of the same fields to get a different result". (The Totals) The answer is very simple. It's not about adding a table in design view or writing the proper code in the SQL statement. It is not about using the Query Wizard because you will get and error message saying something like; you are trying to add two queries from incompatible record sources. When you explained the meaning of returning ONE RECORD and that you "COULD" combine the 2 Queries, the solution was obvious. It was something I had not tried and it was so simple it was stupid on my part. THE ANSWER IS...for those who want to know 1. Click on the Queries Tab on the left side of the Database Window 2. Click New 3. Select Design View 4. Click on the Queries tab on show table box 5. Double Click on the 2 queries that you want to add 6. Double Click on the fields to add them to the design grid 7. Click Run and "It works!" From there it was very easy to get the Percentage I spoke of Originally. 8. Right Click in the Blank Field to the right of the last field in the Query 9. Click "ZOOM" 10. Enter the appropriate expression from your field headings in the Query (The Totals) Like: Percentage: [ ] / [ ] 11. Click Run and "IT'S DONE". You might also want to right click on the Percentage field in the design grid and click properties to set the caption and format of the numbers. Well, Duane, this little exercise has been quite and experience. I want to thank you for not giving up on me. It is the mark of a great teacher to make a student think, and you sure did your job on this one. Let me know how I did... Best Regards, jbeck2010 "Duane Hookom" wrote: jbeck2010, You have to understand basic syntax and the difference between field names with or without spaces. These two are not the same "Customer ID" and "CustomerID". More experienced programmers never create field, table, or object names with spaces. Those that use spaces must place []s around the field names like [Customer ID]. By (returns one record) means that when you display the results of the query (you stated you had two that worked), you only see one record. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, On the 1 issue with the CustomerID, there is a space between Customer and ID in the table. When I type it as it is in the table it gives a syntax error message about the SQL statement and when I type it as CustomerID it gives the result I mentioned before. It doesn't work for me somehow. I must be doing something else wrong. In Regards to the 2 queries, please clarify what you mean by (one record). Query 1 has 4 fields; Customer ID, Name, "Amount Purchased", Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by an Individual from many records. Query 2 has 2 fields; Amount Purchased, Product Type Sum Active And returns the value of the "Sum of Amount Purchased" by everyone from many records. Are you saying, if the queries are pulling information from more than one record you cannot combine the two queries or that if the query produces one total as the result of the query, it is one record, and you can combine the one total result fom each query in another query. Duane, I am very appreciative of your patients with me on this subject and understand if you think I should seek other help. It seems as though this should be very simple, but for some reason I'm just not getting it. Thanks for all your efforts on my behalf, jbeck2010 "Duane Hookom" wrote: If you have two queries where one returns only one record, you can create another query with both queries as the source and then use fields from both source queries. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Just one of my thoughts...It seems as though we kind of got off the subject. As I mentioned earlier, I have already setup the 2 queries that give me the totals I need. The problem is combining those 2 totals in 1 query and producing a percentage based on those 2 totals. The resulting query should look something like this; Customer ID, Customer Name, Amount Puchased, Percentage of Total Sold 1 John Smith 3200 32% Thanks for your time and consideration, jbeck2010 "Duane Hookom" wrote: Make sure my table and field names match your table and field names. Then begin creating a new query and select any table. From the query design view, select to view the SQL view. The replace the existing SQL with: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thank You for your timely response. I'm sorry I have not got back to you sooner with my progress. I've been tied up for a couple of days in meetings on other projects. Your expert guidance on the solution to my problem seems to be the answer, but the real problem is that I don't know the proceedure to use in setting up Queries 1 & 3. Is there a way for a simple minded guy like me to add the fields in the design grid of a Select Query and then inter specific criteria to obtain the needed results. If so, please explain the proceedure and specific criteria and where to indicate it. Duane, I know the above might sound a little dense to someone such as your self, but any further help you can give me would greatly appreciated. I remain your greatlful student, jbeck2010 "Duane Hookom" wrote: Consider a query like: SELECT Sum((CustomerID=1) * [Amount Purchased])/Sum([Amount Purchased]) as PctByCust1 FROM [table 2] WHERE [Product Type]="Active"; If you need this for each customer, create a query "qtotActiveSum" like SELECT Sum([Amount Purchased]) As TotalPurchased FROM [Table 2] WHERE [Product Type]="Active"; Then create another query SELECT CustomerID, Sum([Amount Purchased])/TotalPurchased as PctOfTotal FROM [Table 2], qtotActiveSum WHERE [Product Type]="Active" GROUP BY CustomerID; -- Duane Hookom MS Access MVP -- "jbeck2010" wrote in message ... Duane, *I hope this information will help you determine the correct solution for my problem. You probably have already given it to me, but I don't quite understand how to do it. So, here goes... The Table Structures Table 1 Table 2 Customer Personal Information Product Purchase Information CustomerID (Primary Key) CustomerID (Foreign Key) Name Product (Unique Number for each Purchase) Address Product Type (Active, Canceled, Void City Date Purchased State Price Zip Code Amount Purchased Home Phone Work Phone The above is the basic structure of the 2 tables. They are in a one to many Relationship with table 2 being the many side. What I'm trying to do seems very simple, but somehow I'm just not getting it. As far as the records you requested, I think I can make it very simple. As you look at the above tables a record consists of the information from both tables. The Basic difference in the individual records is who purchased and on what date and the amount. The wildcard values are in the Product Type. (See Table2) *What I am try to do is setup a query that will calculate the percentage between the total amount of product an individual has purchased and the total amount of product that has been purchased by everyone. Query1 Example: CustomerID (1) John Smith 3200 Active Query2 Example: Customers 10000 The result that I am looking for is CustomerID (1) John Smith 3200 32% Active Then I will use the query to generate a report on all individual customers. Note* As customers purchase more product their percentage will automatically update. Duane, I hope this is helpful to you in making your evaluation. As I've said before, I really appreciate your time and advise. Best Regards, jbeck2010 "Duane Hookom" wrote: 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 06:31 PM |
Calculate a Percentage based off one record | G. Wolfe | Setting Up & Running Reports | 2 | December 28th, 2004 10:51 PM |
Financial Comparison | Steven Cheng | Setting Up & Running Reports | 13 | November 26th, 2004 11:59 PM |
Too Few Parameters error Mail Merge Access Parameter Query | Tony_VBACoder | Mailmerge | 3 | September 14th, 2004 12:15 PM |