?
Solved

Query criteria

Posted on 2016-09-08
4
Medium Priority
?
47 Views
Last Modified: 2016-09-09
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
0
Comment
Question by:Kai Lee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 41790235
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.
0
 

Author Comment

by:Kai Lee
ID: 41790262
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.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41790294
For starters, names that include offending characters must ALWAYS be enclosed in square brackets.  Beyond that, they are harder to read and it is easy to insert multiple spaces since they are so small and when you use them in forms or reports, Access substitutes the Underscore for all offending characters so
Lead Time (days)
becomes
Lead_Time__days_
when you reference the control in VBA.

The app will still work with the poorly constructed names even though they will ultimately annoy you if you ever get to coding.  But, it is important to not try to use that denormalized query which is the forecast.  There is no way to join it on date to other tables because it doesn't contain a date.

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
Using Excel as the model is not the way to build an MRP.  Access is a relational database.  It is important that you develop properly normalized tables from the beginning or you will make considerably more work for yourself and some things will simply be impossible such as your current request.  If you think of Access as Excel with more rows, you are in for some serious disappointment and frustration.

Rather than studying Excel samples of MRPs, you are better off researching normalization and understanding how you transform a flat excel file into a properly normalized set of tables.
0
 

Author Comment

by:Kai Lee
ID: 41791215
Thank you Pat
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question