Hi,
Im currently encountering an issue with access and was wondering if anyone had any ideas
My Database is built on linked tables to an excel file which changes weekly. The excel file contains details of customer orders.
Im trying to build a report to detail what supplies i need to meet demand etc.
The reports required output is (Order_Ingredient, Supplier, Monday, Tuesday, Wednesday, Thursday, Friday, Total)
Order_Ingredient can contain different fields from the main orders table(i.e Drink, Snack etc)
The Orders table i am running querys off has the structure (OrderID, Date, Order_Name, Drink, Snack,).
I know i can run crosstab query's on the orders table to get the quantity for days of the week for each order_ingredient.
These come out with the structure (Order_Ingredient, 12/05, 13/05, 14/05, 16/05/ 17/05). I then run querys to add these details to a table which i run the report off.
The issue i am having is when the excel file in the back end changes (i.e. to next weeks), the date names obviously change in the crosstab queries,
which makes the queries on the crosstab queries obsolete.
If anyone has any recommentdations the would be great.
Im currently encountering an issue with access and was wondering if anyone had any ideas
My Database is built on linked tables to an excel file which changes weekly. The excel file contains details of customer orders.
Im trying to build a report to detail what supplies i need to meet demand etc.
The reports required output is (Order_Ingredient, Supplier, Monday, Tuesday, Wednesday, Thursday, Friday, Total)
Order_Ingredient can contain different fields from the main orders table(i.e Drink, Snack etc)
The Orders table i am running querys off has the structure (OrderID, Date, Order_Name, Drink, Snack,).
I know i can run crosstab query's on the orders table to get the quantity for days of the week for each order_ingredient.
These come out with the structure (Order_Ingredient, 12/05, 13/05, 14/05, 16/05/ 17/05). I then run querys to add these details to a table which i run the report off.
The issue i am having is when the excel file in the back end changes (i.e. to next weeks), the date names obviously change in the crosstab queries,
which makes the queries on the crosstab queries obsolete.
If anyone has any recommentdations the would be great.
