A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculate a percentage using 2 different query totals



 
 
Thread Tools Display Modes
  #11  
Old February 27th, 2005, 05:11 PM
jbeck2010
external usenet poster
 
Posts: n/a
Default

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  
Old February 27th, 2005, 07:23 PM
jbeck2010
external usenet poster
 
Posts: n/a
Default

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  
Old February 27th, 2005, 11:58 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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  
Old February 28th, 2005, 12:00 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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  
Old February 28th, 2005, 02:23 AM
jbeck2010
external usenet poster
 
Posts: n/a
Default

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  
Old February 28th, 2005, 03:59 AM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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  
Old February 28th, 2005, 10:03 PM
jbeck2010
external usenet poster
 
Posts: n/a
Default

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  
Old February 28th, 2005, 11:05 PM
Duane Hookom
external usenet poster
 
Posts: n/a
Default

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  
Old March 1st, 2005, 01:51 AM
jbeck2010
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.