Calculate a percentage using 2 different query totals

I have setup 2 separate queries to get specific totals. It is impossible to
combine them into 1, as they both use 1 key field with different criteria to
get the correct total.

Is there any way to calculate and display, in a query or report, a
percentage as it relates to those 2 totals.

Example: Total bought 3,200 Total available 10,000 = 3,200/10,000=32%

All I Need is the percentage to show with the appropriate record, not the
calculation.

Please Help,
jbeck2010

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

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

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

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

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

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

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

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

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