Combine Queries

[Steven.Allman]

I tried this in another thread, but I think I have gone a different route, so I want to start a new question.



Each record has 4 date fields and an invoice received field.
I want a query that looks at all 4 date fields, and generates results if ANY of the four are older than 30 days and have not 'invoice received"

How in the hell would I go about this?

Am i asking way to much or do I need 28 sub queries of queries etc?

[RuralGuy]

Are you using the query builder to create your query?

[NTC]

generically speaking; if you want any of 4 fields to be/meet the criteria then these are OR statements ...meaning that you put in the criteria at each column but be sure to put them all on a separate line - not on the same line (which creates an AND statement). Query design grid offers all those lines just for this purpose. Should be a snap.

[Steven.Allman]

Query Design, not the wizard..

[Steven.Allman]

i guess that is the trick, NTC. I am doing a <Date()-30 AND [past suspense] = "yes" but also have to have OR record 2 is <Date()-30 AND [past suspense] = "yes" OR record 3 <Date()-30 AND [past suspense] = "yes", etc..
problem is, its showing multiple records on the same line.
Its combining my records and I need them kept seperate..
I kind of need someone to tell me how to start this over.. I am about 30 seconds from deleting all my queries and going it again

[TheShabz]

maybe I'm not understanding the issue correctly, but this seems really simple
SELECT *
FROM myTable
WHERE (date1<date()-30 OR date2<date()-30 OR date3<date()-30 OR date4<date()-30) AND invoiceReceived = "no"

[RuralGuy]

maybe I'm not understanding the issue correctly, but this seems really simple
SELECT *
FROM myTable
WHERE (date1<date()-30 OR date2<date()-30 OR date3<date()-30 OR date4<date()-30) AND invoiceReceived = "no"

That is *exactly* what the Query Designer would have done if used correctly.

[Steven.Allman]

Ok, took your guys advice. Its a combination of all these items.
I have an AND statement followed by an OR statement, followed by an AND, followed by an OR, and so on and so forth. Its a much longer statement than I thought it would be, and the results are not what I was hoping for, BUT, i can work with the results quite well. Thanks for your help guys.....

[TheShabz]

if your AND statement is common, then you dont need it after every one. just group together your OR statements with () and put one AND at the end.

[RuralGuy]

...just as Post #6 shows.