Calculate weekly overview query
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Calculate weekly overview query
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
Could you provide an example of what you'd like the output to look like?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
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?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
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?
Where do we find the initial number of units/pallets? Or do we start with 0?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
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.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
OK, but what is the initial situation? 0 units of each product?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
Yes we first order. Later we receive the first goods
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
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?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
No. This is not nescessary. If I have a report like the image above it' 's ok!
Best regards ,
Michiel
Best regards ,
Michiel
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
I need an overview by product.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
So sorted by product and within product by week?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
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.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
I hope this will do more or less what you want...
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
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!
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calculate weekly overview query
Sorry, I don't understand - what exactly do you want to filter?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
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.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Lounger
- Posts: 42
- Joined: 28 Nov 2013, 13:09
Re: Calculate weekly overview query
Thanks a lot! I'm still trying...