How do I pull only the information needed from a Access Query?

By mclemorekr ·
I am creating a Access query to pull a date from 4 different fields (Date1, Date 2, Date 3, Date 4) in the same record. An example of the criteria that I am trying to extract is asking for a specific date for one report and a range of dates for a second report. My problem is that a July date (unwanted date) is showing up and it shouldn't. Also the in the monthly report that I'm asking for a date range of 8/1/11 - 8/31/11, it is showing some unwanted dates from other months that is not what my criteria was asking for. An example of my problem is: Date 1 shows up with a July 2011 date, Date 2, Date 3 and Date 4 shows a date in August like I specified in the criteria. The problem is that I don't want this July date or any other unwanted date showing up and calculating in my report. I just want what I asked for. What am I asking for incorrectly? And how do I extract just what I need? I was using a parameter in my query, is this where I am going wrong? Please help! I am late on providing these reports to a supervisor and I just can't figure out the problem. Thank you in advance.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Request for Clarification

by robo_dev In reply to Clarifications

It might be helpful to post the query.

What data type have you specified in the query parameters?

Collapse -

Request for Clarification

by mclemorekr In reply to Clarifications

You are correct. But I am confused about what I need to do. Today I created 4 different queries (this is what I thought you were telling me to try). Query1: completiondate1and completiontype1; Query 2: completiondate2 and completiontype2; Query 3: completiondate3 and completiontype3; Query 4: completiondate4 and completiontype4. All the dates were within the range I requested. But I do not understand how to bring these numbers together to create my report. I would upload my query but I do not see any import or upload buttons to do so. Thanks in advance!

Collapse -


by Tony Hopkinson In reply to How do I pull only the in ...

I suspect what's happening is
a row meets you criteria and provides you with up to another three dates which may not, anything that effectively takes the four dates in a row and turns them in to a column, is going to look wrong.
What you need to do is switch them before you select e.g.

Select ID, Date1, 1 as DateNumber Where Date1 Between ...
SElect ID,Date2,2 where Date2 Between
SElect ID,Date3,3 where Date3 Between
SElect ID,Date4,4 where Date4 Between

Then you can bag them up into the sets you need to drive the various reports.

Or something like that anyway.


Collapse -

Take a step back

by Tony Hopkinson In reply to How do I pull only the in ...

If the database had been normalised you'd have had something like

JobID, Job Description...

JobStatusKey, JobID, JobStatusType,JobDate

So a report on jobs where there had been some change in April, (or no change in April even), would have been a quick join with a subreport to cope with jobs that changed status more than once in the time period.

So what you have to do is create a query that wll give you the equivalent of the JobStatus table above for your stuff.
After that reporting should be simple it expects and is designed for normalised data.

Related Discussions

Related Forums