Calculate weekly overview query

[mwal]

Hi,

I would like to make a calculation / report on the number of pallets per week. And the number of pallets that are from the start date to the current date stand. I do not know so well how to begin.

I have three tables:

Products (product details, OCVE are the pieces in a outer carton packaging unit)
Inventory Transactions (for the migrations these are the inventory by pieces not packaging units)
product Pallet (for the pallet inventory)

In the inventory details, the migration date. There are intermediate migrations. It would be great if I resolve this issue.

Thanks a lot in advance!

Best regards,
Michiel

[HansV]

Could you provide an example of what you'd like the output to look like?

[mwal]

Hi Hans,

I'll appreciate your help.

I have uploaded a picture. Weekly numbers should do the trick. Somthing like this should be great.
Thanks a lot.

Best regards,
Michiel

[HansV]

I'm afraid I don't see how the tables in your database could produce such an overview. Could you provide an example of how the numbers of available units and pallets could be calculated?

[mwal]

Hi Hans,

Thanks for your reply. I have tried it in the attached database. There are a few queries. I Think I have to Calculate the totall pallets ordered minus the pallets send to the customer. Maybe you can take a look. I 'll find it rather difficult. Query: Week overeenstemmende QueryPallet

and Duplicaten zoeken voor QryPalletTotaal for the totall pallets. I started in QueryPallet for calculating the pallets in and out.

Thanks a lot for your help

[HansV]

Do you want to do this per product or overall?
Where do we find the initial number of units/pallets? Or do we start with 0?

[mwal]

Per product. The pallets are in the table "ProductPallet" and linked to "Products" table. The ordered units are in the table "Inventory Transactions" There are 900 units on 1 pallet. So we can calculate the total pallets.

[HansV]

OK, but what is the initial situation? 0 units of each product?

[mwal]

Yes we first order. Later we receive the first goods

[HansV]

Do you want to report to be sorted by product first, and for each product by week, or by week first, and within each week by product?

[mwal]

No. This is not nescessary. If I have a report like the image above it' 's ok!

Best regards ,
Michiel

[mwal]

I need an overview by product.

[HansV]

So sorted by product and within product by week?

[mwal]

I want to generate a report from the product form. I want an overview by product per week. Just like the above example. So we can calculate the Total pallets on stock by week.

[HansV]

I hope this will do more or less what you want...

weekly.zip

[mwal]

Wow! Thanks a lot! This is very helpfull! But is it possible to filter the output from the DeliveryDate from table products to the current date? I tried it but I failed!

[HansV]

Sorry, I don't understand - what exactly do you want to filter?

[mwal]

I would like to filter the report output results from the DeliveryDate till now. The deliverydate field is in the products table. I dont know how to do.

[HansV]

I'll look at it later this morning.

[mwal]

Thanks a lot! I'm still trying...