Date Question

[Guest]

I have a report that shows 10 fields. The user inputs a date for quarter they
want to see the information broken down. There is a column that should show
everything up until the previous quarter (one total amount-not broken down)
and a column that shows everything up until the previous quarter PLUS the
current information. I have the current working fine, it's the previous
quarter I'm having a problem with.. How would I go about getting this to work?
Thanks.

 

[Allen Browne]

Use a subquery to aggregate the values from the previous quarter.

You will end up typing something like this into the Field row of your query:

PriorQtr: (SELECT Sum(Amount) AS SumOfAmount
FROM Table2 AS Dupe
WHERE Dupe.TransactionDate Between ... And ...)

This expression illustrates how to get the starting date of the previous
quarter:
DateSerial(Year(Date()), 3*(DatePart("q", Date()) - 1) - 3, 1)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

Thanks Allen,
I'll give it a shot!

Use a subquery to aggregate the values from the previous quarter.

You will end up typing something like this into the Field row of your query:

PriorQtr: (SELECT Sum(Amount) AS SumOfAmount
FROM Table2 AS Dupe
WHERE Dupe.TransactionDate Between ... And ...)

This expression illustrates how to get the starting date of the previous
quarter:
DateSerial(Year(Date()), 3*(DatePart("q", Date()) - 1) - 3, 1)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

I would have to do this for all 10 fields? and then do one more field for a
total?
I see you have date/between... can you do a less than... so if you were
looking at the breakdown of Q3 and grand total for Q2, Q2 would be
everything less than 6/30/06?

 

[Allen Browne]

Yes: you will need a subquery for each field that you need to get the
previous quarter's total for. (You don't need an extra subquery to get the
total if it is just the sum of the others.)

Yes: you can use less than (<) in date criteria.

 

[Allen Browne]

No. You cannot use a SELECT statement in the Control Source of a text box or
other control.

You can use it in the RecordSource of the form, of course.

 

[Allen Browne]

Not directly in the report.
You need to create the query to do that.

And in order to create an efficient query, you need a normalized design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

so there's no way this can be accomplished in a report? and I need to show
all records, even if there is no data.

Prev. Qtr Field1T Field1I Field2T Field2T TotalT TotalI TOTAL

for Prev. qtr. you can't use something like:
=([TotalT],q, -1) and =([TotalI]q,-1) of course my syntax stinks, but I
hope you understand what I mean.

No. You cannot use a SELECT statement in the Control Source of a text box
or
other control.

You can use it in the RecordSource of the form, of course.

 

[Allen Browne]

I can't give you a definitive answer without spending time I don't have to
examine your table. It just looks like repeating fields.

If you wish to read further, here's a link to a bunch of links:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

What is considered "normalized design"
I have 18 fields and each one of those fields has another field to put a
zero in the null fields so there are a total of 58 "colummns" in my
query...
I also have a TOTALTAX and a TOTALINTEREST to give me totals of all taxes
and
interest.

tbl_ReserveSchedule
IssueID (relationship to Issue table)
Change Date
Quarter: Format([ChangeDate],"qyyyy") with the [Enter Your Quarter]
Field1Tax
Field1A: IIf([Field1Tax] Is Null,"0",[Field1Tax])
Field1Interest
Field1Tax
Field1B: IIf([Field1Interest] Is Null,"0",[Field1Interest])
etc.
The fields are monetary amounts. How would I create a subquery with this?
The user needs to define the current quarter and the previous quarter
would
be a total of all entries prior to the selected quarter, and I need to
show
all records. This sounds really complicated and it sounds like I'll have a
million "Field:"s listed in my query.

Not directly in the report.
You need to create the query to do that.

And in order to create an efficient query, you need a normalized design.

so there's no way this can be accomplished in a report? and I need to
show
all records, even if there is no data.

Prev. Qtr Field1T Field1I Field2T Field2T TotalT TotalI
TOTAL

for Prev. qtr. you can't use something like:
=([TotalT],q, -1) and =([TotalI]q,-1) of course my syntax stinks, but
I
hope you understand what I mean.

:

No. You cannot use a SELECT statement in the Control Source of a text
box
or
other control.

You can use it in the RecordSource of the form, of course.

I'm sorry about so many questions.
Is there anyway you can use the SELECT statement in the form
itself?