Two simple questions (merge two tables and group problem)

[Guest]

I hope you can help me with the next problems I have:

Question 1: Merge two tables
I have a table with shipped cartons in peak and average month

ShippedAverage
ShippedPeak

I like to have a querry combining both tables like

SELECT * FROM ShippedAverage
AND
SELECT * FROM ShippedPeak

Is this possible? How can I do this in the design view?

Question 2: Group problem

I have two different types of shipping tables

ShippedCartons, with fields

ProductCode, NrShippedCartons
1234 10
1234 15
2345 10

And ShippedPallets
ProductCode, NrShippedPallets
1234 2
1234 4
2345 1

I like to have a summary of both

SELECT ProductInfo.ProductCode,
sum(ShippedCartons.NrShippedCartons),
sum(ShippedPallets.NrShippedPallets)
WHERE productInfo.ProductCode = ShippedCartons.ProductCode
AND productInfo.ProductCode = ShippedPallets.ProductCode
GROUP BY productInfo.ProductCode

To get next info
ProductCode SumOfCartons SumOfPallets
1234 25 6
2345 10 1

But the values are higher, probably because he selects too much records for
the sum (double records?).

How do I solve this?

Thanks in advance again!!

Regards,

Nasko

 

[Guest]

I found out that I can solve the first problem by using the UNION ALL command.

That leaves only the second question...

Regards,

 

[Guest]

Why do you have two different tables for shipped pallets and cartons?

 

[Guest]

To be honoust, this is only an example.

using the real data, one table coming direclty from the client shows the
cartons that are shipped directly and another table gives the cartons that
need extra handling before being shipped. They dont have the same format so I
cannot ´union´ them. I made this example to better explain the problem I
have. (The pallet field came into the example because I have to calculate
everything in pallets at the end...)

Regards,

 

[Guest]

The exact querry. I also have a problem that the sum total pallets is only
calculated when there is copacking for a certain ProductCode.

SELECT ProductCodes.ProductCode,
AllOut.ShippedCartonNr,
AllOut.ShippedInners,
AllOut.ShippedCartonNr Mod ProductCodes.CartonsPerpallet AS
LooseCartons,
ProductCodes.CartonsPerPallet,
AllOut.ShippedCartonNr\ProductCodes.CartonsPerpallet AS FullPallets,
[LooseCartons]/ProductCodes.CartonsPerPallet AS PickedPallets,

AllOut.ShippedInners/ProductCodes.InnersPerCarton/productCodes.CartonsPerPallet AS InnerPallets,
[CopackAll].[ShippedCartonNr]/[ProductCodes].[CartonsPerPallet] AS
CopackPallets,
[FullPallets]+[PickedPallets]+[Innerpallets]+[CopackPallets] AS
TotalPallets
FROM (ProductCodes LEFT JOIN AllOut ON ProductCodes.ProductCode =
AllOut.ProductCode) LEFT JOIN CopackAll ON ProductCodes.ProductCode =
CopackAll.ProductCode;

 

[Guest]

The only suggestion I can make is if you design a query and show the two
tables. Insert the following fields

ShippedCartons.ProductCode
ShippedCartons.SumOfCartons
ShippedPallets.ProductCode (Insert a criteria here:
[shippedcartons].[productcode])
ShippedPallets.SumOfPallets

Hopefully you will then get this:

ProductCode NrShippedCartons ProductCode NrShippedCartons
1234 10 1234 2
1234 15 1234 4
2345 10 2345 1

Then you can make the second "ProductCode" column invisible and base a
totals query on this.

The alternative is to create a join between the two product codes in the top
pane of the query and then you should get the same result.