Software

Ask something
Question Locked

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

0 helpful
5 replies

by mclemorekr

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

5 replies

by robo_dev

It might be helpful to post the query.

What data type have you specified in the query parameters?

by mclemorekr

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!

by tony hopkinson

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 ...
Union
SElect ID,Date2,2 where Date2 Between
Union
SElect ID,Date3,3 where Date3 Between
Union
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.

HtHs

by tony hopkinson

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

Jobs
JobID, Job Description...

JobStatus
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.

This discussion is closed to new replies.