Multiple Queries in one report ?

[Mike Hughes]

I'm trying to create a report from several queries.
The only difference in the querys is the calculation to determine a number of days since the date received.

This is the first query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-182));
I want this in a report

Then I want the report to show the results of this query just below the results of the first query.
This is the second query:
SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-152));

Then I want the report to show the results of this query just below the results of the second query.
This is the third query:

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD]
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-122));

and I want this to go on for 3 more queries just like these except for the neg. number will change. I also want the query results to be seperated from each other by a space or something. Each query uses the same fields from the same table.

Is this something that I should be doing with one or several queries or some other way? Could you help me understand how to do this?

Thanks

Mike

 

[llkhoutx]

Make each query the recordsource for a subreport, with your report being a serier of subreports.

 

[Mike Hughes]

I'm not very experienced at this....could you expand on your reply?
The more detail you have time to give, the better...Thanks

 

[llkhoutx]

Build a separate report for each of your queries. Then create a new report and drag each of the subreports from the database window onto this report.

 

[bjackson]

another way you could approach this is add a field to
your table applications and instead of using subreports
you could use 1 report and use the grouping to split
up the data

when you click to open the report run the querys to update
the table field.give it a value of say 1 for the first query,2 for the
2nd query ,3 for the 3rd query etc,that way you will
be able to have unlimited groups with out the need to
have sub reports

 

[Mike Hughes]

Pat,

I tried you query first and I get a popup box telling me that I need to remove a parentheses in this line after "Older", but when I remove the parentheses and run it, the system tells me to remove another parentheses....until their all gone.

IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 32, "Period6","Older")))))) As Period

Then when their all gone and I run it I get this message:

Wrong number of arugments used with function in query expression:

IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 182

This does appear to be the best way to accomplish what I want but I can't get it to work....any other ideas?

 

[simongallop]

On the first IIf statement remove the bracket .[DATE RECEIVED])<= Iif

This is because the bracket after DATE RECEIVED is closing the Iif statement which is not what you want. { IIf(test, true, false) }

HTH

 

[Mike Hughes]

I now get this message when I run the query:

Syntax error (missing operator)in query expression IIf(tblAPPLICATIONS.DATE RECEIVED)<= Date() – 182.




SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD], IIf(tblAPPLICATIONS.DATE RECEIVED)<= Date() - 182, "Period1", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 152, "Period2", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 122, "Period3", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 92, "Period4", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 62, "Period5", IIf(tblAPPLICATIONS.[DATE RECEIVED])<= Date() - 32, "Period6","Older")))))) As Period
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-32));

 

[simongallop]

Mike,

Sorry I wasn't clear enough in the previous message.

Here are some rules:

[] are used to enclose fields. If the field name is a continuous string then you do not need it, but if there is a space then you do:

eg:
tblAPPLICATIONS.MyContinuousFieldName {OK}
tblAPPLICATIONS.[MyContinuousFieldName] {OK}
tblAPPLICATIONS.My field name with spaces {WRONG}
tblAPPLICATIONS.[My field name with spaces] {OK}

() are usualy used to enclose arguments. In the IIF statement, there has to be 3 parts WITHIN the () brackets, being IIf(Thing under test, Action when true, Action when false) NOTE: The commas are used to seperate the different parts of the statement.

In your example, you removed the field brackets so access is trying to find the function RECEIVED {as that is after the space so is read as an action} Obviously it does not understand so crashes.

SELECT tblAPPLICATIONS.[DATE RECEIVED], tblAPPLICATIONS.MEMBER, tblAPPLICATIONS.SSN, tblAPPLICATIONS.SPECIALIST, tblAPPLICATIONS.[DAYS OLD], IIf(tblAPPLICATIONS.[DATE RECEIVED]<= Date() - 182, "Period1", IIf(tblAPPLICATIONS.[DATE RECEIVED]<= Date() - 152, "Period2", IIf(tblAPPLICATIONS.[DATE RECEIVED]<= Date() - 122, "Period3", IIf(tblAPPLICATIONS.[DATE RECEIVED]<= Date() - 92, "Period4", IIf(tblAPPLICATIONS.[DATE RECEIVED]<= Date() - 62, "Period5", IIf(tblAPPLICATIONS.[DATE RECEIVED]<= Date() - 32, "Period6","Older")))))) As Period
FROM tblAPPLICATIONS
WHERE (((tblAPPLICATIONS.REPORTS)="TIME AGING")) And (((tblAPPLICATIONS.[DATE RECEIVED])<=Date()-32));


HTH

 

[Mike Hughes]

THAT WAS IT...WORKS NOW!!!! THANKS THANKS THANKS !


mIKE

 

[Mike Hughes]

Harry,
I have just one more question on this:

Is there a way to make
Period 1 = Date() which would be 03/07/2003
Period 2 = Date()+30 Todays date plus 30 days
Period 3 = Date()+60 Todays date plus 60 days
Period 4 = Date()+90 Todays date plus 90 days
Period 5 = Date()+120 Todays date plus 120 days
Period 6 = Date()+ 150 Todays date plus 150 days

Rather than say Period 1 on the report it would say 03/07/2003
and for Period 2 it would say 04/06/2003 and so on for each Period? How could I do this.

Thanks
Mike

 

[Mike Hughes]

Pat,

This is very hard to explain what I want the report to do, but here goes.
First
Is this what your saying the query should look like:

SELECT [tblAPPLICATIONS].[DATE RECEIVED], [tblAPPLICATIONS].[MEMBER], [tblAPPLICATIONS].[SSN], [tblAPPLICATIONS].[SPECIALIST], [tblAPPLICATIONS].[DAYS OLD], IIf([tblAPPLICATIONS].[DATE RECEIVED]<=Date()-182,"Date()",IIf([tblAPPLICATIONS].[DATE RECEIVED]<=Date()-152,"Date()+30",IIf([tblAPPLICATIONS].[DATE RECEIVED]<=Date()-122,"Date()+60",IIf([tblAPPLICATIONS].[DATE RECEIVED]<=Date()-92,"Date()+90",IIf([tblAPPLICATIONS].[DATE RECEIVED]<=Date()-62,"Date()+120",IIf([tblAPPLICATIONS].[DATE RECEIVED]<=Date()-32,"Date()+150","Older")))))) AS Period
FROM tblAPPLICATIONS
WHERE ((([tblAPPLICATIONS].[DATE RECEIVED])<=Date()-32) And (([tblAPPLICATIONS].[REPORTS])="TIME AGING"));

I want the first group in the report to be cases that are 6 months or more older today (dateof the report).

I want the second group in the report to be cases that will be 6months old, thirty days from today (date of the report)

I want the third group in the report to be cases that will be 6months old ,60 days from today (date of the report)

I want the fourth group in the report to be cases that will be 6months old ,90 days from today (date of the report)

I want the fifth group in the report to be cases that will be 6months old, 120 days from today (date of the report)

I want the sixth group in the report to be cases that will be 6months old 150 ,days from today (date of the report)

So the complete report would contain groups of cases that would be 6 months or more old on today, one month from today,two months from today,three months from today, etc.....

Right now each group is name Period 1, Period 2, Period 3, etc.
I would like them to be named
Period 1 Today
Period 2 the date 30 days from today
Period 3 the date 60 days from today
Period 4 the date 90 days from today
Period 5 the date 120 days from today
Period 6 the date 150 days from today

And the dates would change bases on what the date is when the report is run.

I don't know if this can be done or not...as you can tell I don't know much about this type of thing.

What do you think?

Mike

 

[Mike Hughes]

Works great , Thanks to ALL!

Mike