Link to home
Start Free TrialLog in
Avatar of Kai Lee
Kai Lee

asked on

Query criteria

Hello Experts,

I am working on a simple query that will do some +/- calculation but I don't know hot to set up my criteria. I want to have all my dates can correspond to the month in Tool Forecast Demand query. I attached a picture of my query. If you don't understand my question, feel free asking me. Thank you.
Que.PNG
Avatar of PatHartman
PatHartman
Flag of United States of America image

I'm not quite sure what you want the query to do but you will have an easier time of it if you normalize the forecast table.  It should be:

ToolNumber
ForecastYear
ForecastMonth
ForecastQty

And the primary key should be a compound key on ToolNumber,ForecastYear, and ForecastMonth.  Use shift-click to highlight all three columns and press the key button to create a compound primary key.  The data  will now give you one row per month per tool number.  THEN you will be able to do queries that group the quantities from the order table by month.  Then you can match forecast against actual quantities if that is what you are aiming for.

You have other issues also.  Neither the forecast or planned tables has a primary key and your table and column names contain embedded spaces and even worse - special characters.  I also don't understand why the "planned" fields are in two tables or what ScheduledReception,  PlannedOrderRelease, and PlannedOrderReception are.  It feels like they should be quantity fields but why would there be three?  You might order 35 and receive 34 so it could make sense to have separate ordered and received quantities but why would you plan to receive a different quantity than you ordered?  Perhaps it is just your business but with a cursory look, it just doesn't make sense.
Avatar of Kai Lee
Kai Lee

ASKER

Thank you PatHartman for replying me. Order status is a table. Tool Forecast Demand and Planned Order Date are queries. I need the calculated numbers from them in order to move on.  I am basically building an MRP. I don't know if you have heard of it. if you can google it online, there will be some simple excel sheets. I think that might be able to answer some of your questions. Can you tell me why I shouldn't contain spaces and special characters?  Sorry, I am an Access noob.
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kai Lee

ASKER

Thank you Pat