I NEED HELP WITH SUMMARY OF TWO (2) TABLE COMBINING INFORMATION

  • Thread starter Thread starter SuperNerd
  • Start date Start date
S

SuperNerd

I have two table that have the same fields in each but for different uses.
Each table contains the "Suppliers", "Reciept date", "Quantity Recieved",
"Quantity Rejected". What I want to do with this is to have a summary in a
report or form. This summary has to combine the two tables together; if for
example tabel1 has supplier A and recieve date is in April and tabel2 has
supplier A and recieved date in April, then the two will be add
together...this goes the same for the rejected quantity.
I want to add all the amount recieved from the same supplier within the same
month together.
At last, I want to have the total of "Quantity Rejected" divide by "Quantity
Recieved" to get my wanted value, but it has to be a value for each month
differently.

I know, I fail to provide a better explaination this problem but I hope
someone out there can understand me; if not just ""ASK"".

Thanks in Advance for any Help.
 
I have two table that have the same fields in each but for different uses..  
Each table contains the "Suppliers", "Reciept date", "Quantity Recieved",
"Quantity Rejected".  What I want to do with this is to have a summary in a
report or form.  This summary has to combine the two tables together; if for
example tabel1 has supplier A and recieve date is in April and tabel2 has
supplier A and recieved date in April, then the two will be add
together...this goes the same for the rejected quantity.  
I want to add all the amount recieved from the same supplier within the same
month together.
At last, I want to have the total of "Quantity Rejected" divide by "Quantity
Recieved" to get my wanted value, but it has to be a value for each month
differently.

I know, I fail to provide a better explaination this problem but I hope
someone out there can understand me; if not just ""ASK"".  

Thanks in Advance for any Help.
Click to expand...

first things first. You need to get the data from the two tables into
one recordset (bunch of records). So you need to query the two tables

SELECT Suppliers, [Reciept date], [Quantity Recieved], [Quantity
Rejected]
FROM Table1
UNION ALL
SELECT Suppliers, [Reciept date], [Quantity Recieved], [Quantity
Rejected]
FROM Table2;

then you can base your report on this query, and group by Supplier,
Receipt Date...
 
First of all, thanks for the quick respond...
2ndly
How do you get the "group by" to appear in the designe window



[email protected] said:
I have two table that have the same fields in each but for different uses..
Each table contains the "Suppliers", "Reciept date", "Quantity Recieved",
"Quantity Rejected". What I want to do with this is to have a summary in a
report or form. This summary has to combine the two tables together; if for
example tabel1 has supplier A and recieve date is in April and tabel2 has
supplier A and recieved date in April, then the two will be add
together...this goes the same for the rejected quantity.
I want to add all the amount recieved from the same supplier within the same
month together.
At last, I want to have the total of "Quantity Rejected" divide by "Quantity
Recieved" to get my wanted value, but it has to be a value for each month
differently.

I know, I fail to provide a better explaination this problem but I hope
someone out there can understand me; if not just ""ASK"".

Thanks in Advance for any Help.
Click to expand...

first things first. You need to get the data from the two tables into
one recordset (bunch of records). So you need to query the two tables

SELECT Suppliers, [Reciept date], [Quantity Recieved], [Quantity
Rejected]
FROM Table1
UNION ALL
SELECT Suppliers, [Reciept date], [Quantity Recieved], [Quantity
Rejected]
FROM Table2;

then you can base your report on this query, and group by Supplier,
Receipt Date...
Click to expand...
 
First of all, thanks for the quick respond...
2ndly
How do you get the "group by" to appear in the designe window
Click to expand...

Click the "Totals" query icon (looks like a sideways M).
 
Here is the code I have but when it execute, a parameter value comes up?

SELECT Supplier, [Receipt Date], [Quantity units], [Quantity Rejected] FROM
Log
UNION ALL SELECT Supplier, [Receipt Date], [Quantity units], [Quantity
Rejected] FROM CHERY_Log;
 
Would you care to share what the PARAMETER request is? Usually this indicates
a misspelled field name. For instance you have a field named Receipt Date
with two spaces in the first query and one named Receipt Date with one space
in the second query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Here is the code I have but when it execute, a parameter value comes up?

SELECT Supplier, [Receipt Date], [Quantity units], [Quantity Rejected] FROM
Log
UNION ALL SELECT Supplier, [Receipt Date], [Quantity units], [Quantity
Rejected] FROM CHERY_Log;




SuperNerd said:
I have two table that have the same fields in each but for different uses.
Each table contains the "Suppliers", "Reciept date", "Quantity Recieved",
"Quantity Rejected". What I want to do with this is to have a summary in a
report or form. This summary has to combine the two tables together; if for
example tabel1 has supplier A and recieve date is in April and tabel2 has
supplier A and recieved date in April, then the two will be add
together...this goes the same for the rejected quantity.
I want to add all the amount recieved from the same supplier within the same
month together.
At last, I want to have the total of "Quantity Rejected" divide by "Quantity
Recieved" to get my wanted value, but it has to be a value for each month
differently.

I know, I fail to provide a better explaination this problem but I hope
someone out there can understand me; if not just ""ASK"".

Thanks in Advance for any Help.
Click to expand...
Click to expand...
 
John Spencer said:
Would you care to share what the PARAMETER request is? Usually this indicates
a misspelled field name. For instance you have a field named Receipt Date
with two spaces in the first query and one named Receipt Date with one space
in the second query.
Click to expand...

Which brings up a good point... Generally speaking, using spaces in field,
control and variable names is not a good idea. Doing so forces you to use
brackets ("[" and "]") to enclose the name, and also leaves one open to silly
errors (like too many spaces in a name). Instead, of [Receipt Date] try
using ReceiptDate or even Receipt_Date. It's still easy to read and
understand, and is much easier to work with.

Just a little something I recently learned the hard way. Hope that helps...

Regards, Chris
 
Back
Top