Re: Calculate a percentage using 2 different query totals

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: jbeck2010 (jbeck2010_at_discussions.microsoft.com)
Date: 02/28/05 

Date: Mon, 28 Feb 2005 13:03:02 -0800

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" <jbeck2010@discussions.microsoft.com> wrote in message
> news:77794950-5F4A-4987-91AB-B63927F6C4D2@microsoft.com...
> > 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" <jbeck2010@discussions.microsoft.com> wrote in message
> >> news:4608C1F7-52C1-4D2A-9120-A310000B300B@microsoft.com...
> >> > 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" <jbeck2010@discussions.microsoft.com> wrote in message
> >> >> news:803B550B-CB03-4F95-BC34-0D0315273A34@microsoft.com...
> >> >> > 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" <jbeck2010@discussions.microsoft.com> wrote in message
> >> >> >> news:AB0C6DBF-6505-412D-86A7-17AA65670E12@microsoft.com...
> >> >> >> > 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" <jbeck2010@discussions.microsoft.com> wrote in
> >> >> >> >> message
> >> >> >> >> news:6851D633-067B-487D-A2BD-AD30DED70F70@microsoft.com...
> >> >> >> >> > 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" <jbeck2010@discussions.microsoft.com> wrote in
> >> >> >> >> >> message
> >> >> >> >> >> news:B611366A-FD84-4CDF-99BC-08C4FDBCFA6F@microsoft.com...
> >> >> >> >> >> > 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" <jbeck2010@discussions.microsoft.com> wrote in
> >> >> >> >> >> >> message
> >> >> >> >> >> >> news:0549E407-8691-48AB-85EC-267CEAF2BAE7@microsoft.com...
> >> >> >> >> >> >> >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
> >> >> >> >> >> >> >
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: Calculate a percentage using 2 different query totals
    ... Duane Hookom ... It is not about using the Query Wizard because you will ... > queries from incompatible record sources. ... >> field names like [Customer ID]. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Getting a total control to find totals for last 7 days via a q
    ... A different query will be needed that groups on ... these other two queries. ... how would i translate that into an unbound control on my form? ... But this returns the totals for All dates, and not the last 7 days, but if i ...
    (microsoft.public.access.queries)
  • Re: save table records from select tempquery
    ... Append query help is right choice for my case. ... my accountant's room) and trying understand best design solution ... sum totals or recall selected aggregated totals as necessary for later ... No, "MakeTable" Queries, just as the name suggests, make an entirely new ...
    (comp.databases.ms-access)
  • Re: Calculations in Forms
    ... You can use queries ... to extract whatever information you need from the database and then you base ... Open a new query in design view and add your customertable to the design ... you need a totals query to give you daily totals and a ...
    (microsoft.public.access.gettingstarted)
  • RE: Automatic Data Transfer
    ... Otherwise you'd need to do a union query to ... You may have a chains of queries and tables all combining to make your ... The totals probably dont need to stored bec. ... > found that when i try to link multiple summary queries in to a single form ...
    (microsoft.public.access.modulesdaovba)