Complex Query - Date Comparison (1 Viewer)

[michaelfairburn]

Hello All,

I am stuck! I have a linked Excel table in which there are over 100 rows and 60 columns. Each of the rows represents a project and each of the columns represents an attribute of that project.

My problem is this: Each row has details of 6 governance 'gates'. Each 'gate' has 3 date options - initial, revised and actual. I want to run a query on the data that will tell me which project is coming up to a gate and which gate it is. If it's already been through a gate it will have its 'actual' date filled in, obviously.

I need to be able to produce a report for eventual mail merging to send to the project managers to remind them that they're coming up to a gate and to book them into the appropriate gate forum.

I have absolutely no idea how to get ONLY the gates with approaching dates out of the data. I can easily get the whole record out if one of the dates happens to be within a certain parameter (>Now() AND <=Now()+14), using the OR function for each of the gate, but that gives me every gate date, the one I want and the ones I don't.

I probable haven't explained this very well, so please feel free to ask questions. Any help you can give me would be gratefully received as I'm rapidly tearing out what little hair I have left!

 

[vbaInet]

Not quite getting what you want. Could you write in pseudocode what criteria you would like to have?

 

[michaelfairburn]

Ha, I thought maybe I hadn't explained it very well

So, I guess, this is what I'm looking for:

FROM EACH Project, SELECT those dates from EACH GATE (of 6 gates) (with dates representing Initial, Revised, Actual for each gate) that correspond to a parameter and then show ONLY those dates (mapped to the gate) that are valid in a report.

Does that make sense?

Mike

 

[gemma-the-husky]

get a query, try putting different dates in the criteria space to test the data you have, so that you understand exactly how the query works.

enter dates in ## characters - ie #12/1/09#

you can use operators like > (greater than) < (less than) the word between etc etc

when you are fully happy that you can manage your results

then parameterize this by using a form to enter a date, that you can use as a varaible in your query.

 

[michaelfairburn]

Thanks Dave, but I've managed to get that far. In fact, using the >Now() AND <=now()+14 type of parameter in each of the date fields (effectively using the OR command) I can get the [very large] query to pull out all those projects that have a gate coming up.

The problem I have is that I don't want all the other gate information too, as the query pulls out the entire record. I just want to show the project name and the name and the date of the gate that they're coming up to.

 

[jdraw]

Thanks Dave, but I've managed to get that far. In fact, using the >Now() AND <=now()+14 type of parameter in each of the date fields (effectively using the OR command) I can get the [very large] query to pull out all those projects that have a gate coming up.

The problem I have is that I don't want all the other gate information too, as the query pulls out the entire record. I just want to show the project name and the name and the date of the gate that they're coming up to.

Please show us the SQL you are using currently. Some modifications to that SQL will likely solve your problem.

 

[michaelfairburn]

OK, so here's the SQL copied from the query:

SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PRP Initial Date], tblLinkedMasterData.[PRP Revised Date], tblLinkedMasterData.[RFD Initial Date], tblLinkedMasterData.[RFD Revised Date], tblLinkedMasterData.[RFV Initial Date], tblLinkedMasterData.[RFV Revised Date], tblLinkedMasterData.[RFA Initial Date], tblLinkedMasterData.[RFA Revised Date], tblLinkedMasterData.[RFS Initial Date], tblLinkedMasterData.[RFS Revised Date], tblLinkedMasterData.[PR5 Initial Date], tblLinkedMasterData.[PR5 Revised Date]
FROM tblLinkedMasterData
GROUP BY tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PRP Initial Date], tblLinkedMasterData.[PRP Revised Date], tblLinkedMasterData.[RFD Initial Date], tblLinkedMasterData.[RFD Revised Date], tblLinkedMasterData.[RFV Initial Date], tblLinkedMasterData.[RFV Revised Date], tblLinkedMasterData.[RFA Initial Date], tblLinkedMasterData.[RFA Revised Date], tblLinkedMasterData.[RFS Initial Date], tblLinkedMasterData.[RFS Revised Date], tblLinkedMasterData.[PR5 Initial Date], tblLinkedMasterData.[PR5 Revised Date], tblLinkedMasterData.[PM Management]
HAVING (((tblLinkedMasterData.[PRP Initial Date])>=Now() And (tblLinkedMasterData.[PRP Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[PRP Revised Date])>=Now() And (tblLinkedMasterData.[PRP Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFS Revised Date])>=Now() And (tblLinkedMasterData.[RFS Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFD Initial Date])>=Now() And (tblLinkedMasterData.[RFD Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[PR5 Initial Date])>=Now() And (tblLinkedMasterData.[PR5 Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFD Revised Date])>=Now() And (tblLinkedMasterData.[RFD Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[PR5 Revised Date])>=Now() And (tblLinkedMasterData.[PR5 Revised Date])<=Now()+14) AND (("backcolor")="green")) OR (((tblLinkedMasterData.[RFV Initial Date])>=Now() And (tblLinkedMasterData.[RFV Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFV Revised Date])>=Now() And (tblLinkedMasterData.[RFV Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFA Initial Date])>=Now() And (tblLinkedMasterData.[RFA Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFA Revised Date])>=Now() And (tblLinkedMasterData.[RFA Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFS Initial Date])>=Now() And (tblLinkedMasterData.[RFS Initial Date])<=Now()+14));

 

[jdraw]

OK, so here's the SQL copied from the query:

SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PRP Initial Date], tblLinkedMasterData.[PRP Revised Date], tblLinkedMasterData.[RFD Initial Date], tblLinkedMasterData.[RFD Revised Date], tblLinkedMasterData.[RFV Initial Date], tblLinkedMasterData.[RFV Revised Date], tblLinkedMasterData.[RFA Initial Date], tblLinkedMasterData.[RFA Revised Date], tblLinkedMasterData.[RFS Initial Date], tblLinkedMasterData.[RFS Revised Date], tblLinkedMasterData.[PR5 Initial Date], tblLinkedMasterData.[PR5 Revised Date]
FROM tblLinkedMasterData
GROUP BY tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PRP Initial Date], tblLinkedMasterData.[PRP Revised Date], tblLinkedMasterData.[RFD Initial Date], tblLinkedMasterData.[RFD Revised Date], tblLinkedMasterData.[RFV Initial Date], tblLinkedMasterData.[RFV Revised Date], tblLinkedMasterData.[RFA Initial Date], tblLinkedMasterData.[RFA Revised Date], tblLinkedMasterData.[RFS Initial Date], tblLinkedMasterData.[RFS Revised Date], tblLinkedMasterData.[PR5 Initial Date], tblLinkedMasterData.[PR5 Revised Date], tblLinkedMasterData.[PM Management]
HAVING (((tblLinkedMasterData.[PRP Initial Date])>=Now() And (tblLinkedMasterData.[PRP Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[PRP Revised Date])>=Now() And (tblLinkedMasterData.[PRP Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFS Revised Date])>=Now() And (tblLinkedMasterData.[RFS Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFD Initial Date])>=Now() And (tblLinkedMasterData.[RFD Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[PR5 Initial Date])>=Now() And (tblLinkedMasterData.[PR5 Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFD Revised Date])>=Now() And (tblLinkedMasterData.[RFD Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[PR5 Revised Date])>=Now() And (tblLinkedMasterData.[PR5 Revised Date])<=Now()+14) AND (("backcolor")="green")) OR (((tblLinkedMasterData.[RFV Initial Date])>=Now() And (tblLinkedMasterData.[RFV Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFV Revised Date])>=Now() And (tblLinkedMasterData.[RFV Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFA Initial Date])>=Now() And (tblLinkedMasterData.[RFA Initial Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFA Revised Date])>=Now() And (tblLinkedMasterData.[RFA Revised Date])<=Now()+14)) OR (((tblLinkedMasterData.[RFS Initial Date])>=Now() And (tblLinkedMasterData.[RFS Initial Date])<=Now()+14));

This part of the query show the fields you are requesting

SELECT
tblLinkedMasterData.[Project name]
, tblLinkedMasterData.[Segment Project manager name]
, tblLinkedMasterData.[PRP Initial Date]
, tblLinkedMasterData.[PRP Revised Date]
, tblLinkedMasterData.[RFD Initial Date]
, tblLinkedMasterData.[RFD Revised Date]
, tblLinkedMasterData.[RFV Initial Date]
, tblLinkedMasterData.[RFV Revised Date]
, tblLinkedMasterData.[RFA Initial Date]
, tblLinkedMasterData.[RFA Revised Date]
, tblLinkedMasterData.[RFS Initial Date]
, tblLinkedMasterData.[RFS Revised Date]
, tblLinkedMasterData.[PR5 Initial Date]
, tblLinkedMasterData.[PR5 Revised Date]

so if you don't want all these fields, you'll have to remove those you don't want.

 

[michaelfairburn]

Thanks jdraw, I got that, but I don't know which fields I want until I work out which of them is valid. For example, one project may be coming up to the RFV gate, in which case either of these fields ( tblLinkedMasterData.[RFV Initial Date], tblLinkedMasterData.[RFV Revised Date]) could have a date that matches the criteria and another project may be coming up to the RFS gate, in which case either of these gates (tblLinkedMasterData.[RFS Initial Date], tblLinkedMasterData.[RFS Revised Date]) could be valid.

In either case (and there are over 100 projects) I only want to display the project name, project manager and the gate and date that the project is approaching.

Mike

 

[Brianwarnock]

It seems to me that you are going to need 6 queries , 1 for each gate, inorder to avoid all the extraneous information, but you should be able, I think. to use UNION to end up with 1 query on which to base your report.

Also use Date() instead of NOW() unless time is important to you and is in therecord date fields, if it isn't

tblLinkedMasterData.[RFS Initial Date])>=Now()

will never pick up the first day as a date field without a time defaults to 00:00:00

Brian

 

[michaelfairburn]

Thanks Brian. I thought I might have to end up with a whole bunch of queries. I'm not knowledgeable enough to do union queries; could anyone point me to a good site where I might learn how to do them? Thanks

Mike

 

[Brianwarnock]

Help seems to explain them fairly well.

Brian

 

[michaelfairburn]

OK, so I've created 2 separate queries for 2 gates and they each give me the results I would expect. I have then joined the queries using the UNION command (see below) as suggested by Brian above. This ALMOST gives me the result I need EXCEPT that the column heading are those of the first query only as I need to know what gate a project is coming up to. This will have even more significance when I add the queries for the other gates. All help gratefully received.

SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PM Management], tblLinkedMasterData.[Program Name1], tblLinkedMasterData.[RFD Initial Date]
FROM tblLinkedMasterData
WHERE (((tblLinkedMasterData.[RFD Initial Date])>=Now() And (tblLinkedMasterData.[RFD Initial Date])<=Now()+14))

UNION
SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PM Management], tblLinkedMasterData.[Program Name1], tblLinkedMasterData.[RFD Revised Date]
FROM tblLinkedMasterData
WHERE (((tblLinkedMasterData.[RFD Revised Date])>=Now() And (tblLinkedMasterData.[RFD Revised Date])<=Now()+14))

 

[JANR]

Try and aliase an extra field for each Union:

SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PM Management], tblLinkedMasterData.[Program Name1], tblLinkedMasterData.[RFD Initial Date], "Initial" As Gate
FROM tblLinkedMasterData
WHERE (((tblLinkedMasterData.[RFD Initial Date])>=Now() And (tblLinkedMasterData.[RFD Initial Date])<=Now()+14))

UNION
SELECT tblLinkedMasterData.[Project name], tblLinkedMasterData.[Segment Project manager name], tblLinkedMasterData.[PM Management], tblLinkedMasterData.[Program Name1], tblLinkedMasterData.[RFD Revised Date], "Revised" As Gate
FROM tblLinkedMasterData
WHERE (((tblLinkedMasterData.[RFD Revised Date])>=Now() And (tblLinkedMasterData.[RFD Revised Date])<=Now()+14)

JR

 

[michaelfairburn]

Thanks JANR, I'll try that.

Mike

 

[JANR]

It's possible that you must use brackets around the alias [Gate]

"Revised" as [Gate]

JR

 

[Brianwarnock]

It's possible that you must use brackets around the alias [Gate]

JR

It works either way.

Michael
I notice that you have not commented on the Date() v Now() issue.

Brian

 

[michaelfairburn]

Hi Brian,

I tired the date() and it didn't work. Maybe I did something wrong; I just changed now() to date(). Was that right?

Mike

 

[Rich]

If Date() doesn't work then you have a broken/missing reference

 

[michaelfairburn]

Reference to what Rich? I just replaced the Now() with Date() in the SQL above.

Mike