Do Access Reports recalculate their record source?

K

Keith

I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
 
K

Klatuu

Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


Keith said:
I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
 
K

Keith

Dave,

Thanks for the reply.

Changing the query to a make table will "lock" the value of the release_inv
field, therefore eliminating my concern over the value being recalculated.
If it was a single running sum or a count type of value, this wouldn't be
relevant. I only care about the sort by of the query since it affects the
value of the release_inv field.

Anyway, unless I can figure out how to make the query lock in the values
prior to sending the data to the report, I'll just do the make table thing.

Thanks again,

Keith


Klatuu said:
Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


Keith said:
I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
Click to expand...
 
K

Klatuu

Rather than using a make table query, I would suggest a query based on your
current query. Avoid make table queries. They are very inefficient and
contribute heavily to bloat.
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

Thanks for the reply.

Changing the query to a make table will "lock" the value of the release_inv
field, therefore eliminating my concern over the value being recalculated.
If it was a single running sum or a count type of value, this wouldn't be
relevant. I only care about the sort by of the query since it affects the
value of the release_inv field.

Anyway, unless I can figure out how to make the query lock in the values
prior to sending the data to the report, I'll just do the make table thing.

Thanks again,

Keith


Klatuu said:
Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


Keith said:
I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
Click to expand...
Click to expand...
 
K

Keith

Dave,

Thanks again for your input. I tried basing the report on a query based on
my current query, but I got the same results. I believe I had tried that in
the distant past when a similar situation arose. I too hoped that the
intermediate query would prevent the recalculation, but it did not. I
realize that make table queries are ugly, but sometimes there is no practical
way around them (or at least none that I know of).

Thank again,

Keith


Klatuu said:
Rather than using a make table query, I would suggest a query based on your
current query. Avoid make table queries. They are very inefficient and
contribute heavily to bloat.
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

Thanks for the reply.

Changing the query to a make table will "lock" the value of the release_inv
field, therefore eliminating my concern over the value being recalculated.
If it was a single running sum or a count type of value, this wouldn't be
relevant. I only care about the sort by of the query since it affects the
value of the release_inv field.

Anyway, unless I can figure out how to make the query lock in the values
prior to sending the data to the report, I'll just do the make table thing.

Thanks again,

Keith


Klatuu said:
Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


:

I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Well, there is a way around a make table query
(have you figured out I really don't like make table queries and I will do
anything to avoid them)

Create a table to use for the report. Define your fields to the correct
data types and text fields to the correct lengths. (here is where make tables
start to go bad, all text fields default to the size defined in your database
options, and numeric fields can get the type wrong).

Now all you have to do is delete the data out of the table before you run an
append query to populate the table. An Append query is just like a make
table, except is uses an existing table. So, to delete all the data all you
need is:

Currentdb.Execute("DELETE * FROM TableName;"), dbFailOnError

(I'm tryin' to save you from fallin' in to evil deeds) <g>
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

Thanks again for your input. I tried basing the report on a query based on
my current query, but I got the same results. I believe I had tried that in
the distant past when a similar situation arose. I too hoped that the
intermediate query would prevent the recalculation, but it did not. I
realize that make table queries are ugly, but sometimes there is no practical
way around them (or at least none that I know of).

Thank again,

Keith


Klatuu said:
Rather than using a make table query, I would suggest a query based on your
current query. Avoid make table queries. They are very inefficient and
contribute heavily to bloat.
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

Thanks for the reply.

Changing the query to a make table will "lock" the value of the release_inv
field, therefore eliminating my concern over the value being recalculated.
If it was a single running sum or a count type of value, this wouldn't be
relevant. I only care about the sort by of the query since it affects the
value of the release_inv field.

Anyway, unless I can figure out how to make the query lock in the values
prior to sending the data to the report, I'll just do the make table thing.

Thanks again,

Keith


:

Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


:

I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
Click to expand...
Click to expand...
 
K

Keith

Dave,

I'll try that instead.

Once again, I appreciate all of you help with this.

Sincerely,

Keith


Klatuu said:
Well, there is a way around a make table query
(have you figured out I really don't like make table queries and I will do
anything to avoid them)

Create a table to use for the report. Define your fields to the correct
data types and text fields to the correct lengths. (here is where make tables
start to go bad, all text fields default to the size defined in your database
options, and numeric fields can get the type wrong).

Now all you have to do is delete the data out of the table before you run an
append query to populate the table. An Append query is just like a make
table, except is uses an existing table. So, to delete all the data all you
need is:

Currentdb.Execute("DELETE * FROM TableName;"), dbFailOnError

(I'm tryin' to save you from fallin' in to evil deeds) <g>
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

Thanks again for your input. I tried basing the report on a query based on
my current query, but I got the same results. I believe I had tried that in
the distant past when a similar situation arose. I too hoped that the
intermediate query would prevent the recalculation, but it did not. I
realize that make table queries are ugly, but sometimes there is no practical
way around them (or at least none that I know of).

Thank again,

Keith


Klatuu said:
Rather than using a make table query, I would suggest a query based on your
current query. Avoid make table queries. They are very inefficient and
contribute heavily to bloat.
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Thanks for the reply.

Changing the query to a make table will "lock" the value of the release_inv
field, therefore eliminating my concern over the value being recalculated.
If it was a single running sum or a count type of value, this wouldn't be
relevant. I only care about the sort by of the query since it affects the
value of the release_inv field.

Anyway, unless I can figure out how to make the query lock in the values
prior to sending the data to the report, I'll just do the make table thing.

Thanks again,

Keith


:

Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


:

I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Glad I could help. Post back if you have any problems with it.
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

I'll try that instead.

Once again, I appreciate all of you help with this.

Sincerely,

Keith


Klatuu said:
Well, there is a way around a make table query
(have you figured out I really don't like make table queries and I will do
anything to avoid them)

Create a table to use for the report. Define your fields to the correct
data types and text fields to the correct lengths. (here is where make tables
start to go bad, all text fields default to the size defined in your database
options, and numeric fields can get the type wrong).

Now all you have to do is delete the data out of the table before you run an
append query to populate the table. An Append query is just like a make
table, except is uses an existing table. So, to delete all the data all you
need is:

Currentdb.Execute("DELETE * FROM TableName;"), dbFailOnError

(I'm tryin' to save you from fallin' in to evil deeds) <g>
--
Dave Hargis, Microsoft Access MVP


Keith said:
Dave,

Thanks again for your input. I tried basing the report on a query based on
my current query, but I got the same results. I believe I had tried that in
the distant past when a similar situation arose. I too hoped that the
intermediate query would prevent the recalculation, but it did not. I
realize that make table queries are ugly, but sometimes there is no practical
way around them (or at least none that I know of).

Thank again,

Keith


:

Rather than using a make table query, I would suggest a query based on your
current query. Avoid make table queries. They are very inefficient and
contribute heavily to bloat.
--
Dave Hargis, Microsoft Access MVP


:

Dave,

Thanks for the reply.

Changing the query to a make table will "lock" the value of the release_inv
field, therefore eliminating my concern over the value being recalculated.
If it was a single running sum or a count type of value, this wouldn't be
relevant. I only care about the sort by of the query since it affects the
value of the release_inv field.

Anyway, unless I can figure out how to make the query lock in the values
prior to sending the data to the report, I'll just do the make table thing.

Thanks again,

Keith


:

Reports ignore any sorting in a query, so a make table will not improve the
situation. Use the report Sorting and Grouping to order the report. I can't
remember off the top of my head if it is possible to use two fields in the
sorting and grouping, but I think I remember having that problem, so what I
did was create a calculated field in my query that concatenated the two
fields and used that field in the sorting and grouping.
--
Dave Hargis, Microsoft Access MVP


:

I have a report in Access that is based on the following query:

SELECT [OPEN JOBS].CUSTOMER, [PART MASTER].[REF #], [OPEN JOBS].PART_NO,
[OPEN JOBS].JOB_NO, [OPEN JOB RELEASES].QTY, [OPEN JOB RELEASES].[DUE DATE],
JOB_INVENTORY([open jobs].[JOB_NO],[REF #],[INVENTORY COUNT],[QTY]) AS
release_inv, [PART MASTER].[INVENTORY COUNT], [PART MASTER].[PART
DESCRIPTION], [OPEN JOBS].[BLANKET ORDER], [OPEN JOBS].[UNIT PRICE], [UNIT
PRICE]*[QTY] AS ShipmentPrice, [OPEN JOBS].On_Hold, IIf([open
jobs].[CUSTOMER]="Cessna Aircraft Company",[DUE DATE]-7,"") AS CessnaShipDate
FROM ([OPEN JOB RELEASES] INNER JOIN ([PART MASTER] INNER JOIN [OPEN JOBS]
ON ([PART MASTER].CUSTOMER = [OPEN JOBS].CUSTOMER) AND ([PART MASTER].PART_NO
= [OPEN JOBS].PART_NO)) ON [OPEN JOB RELEASES].JOB_NO = [OPEN JOBS].JOB_NO)
INNER JOIN qryOpenJobReleasesSumNotFilled ON [OPEN JOBS].JOB_NO =
qryOpenJobReleasesSumNotFilled.JOB_NO
WHERE ((([OPEN JOB RELEASES].[DUE DATE])<=[Enter Date]) AND (([OPEN
JOBS].[BLANKET ORDER])=No) AND (([OPEN JOBS].[UNIT PRICE])<>0.001) AND
(([OPEN JOBS].On_Hold)=No) AND (([OPEN JOB RELEASES].FILLED)=No) AND (([OPEN
JOBS].[CLOSE DATE]) Is Null))
ORDER BY [PART MASTER].[REF #], [OPEN JOB RELEASES].[DUE DATE];

The query field release_inv require the data to be sorted as the ORDER BY
statement specifies (first by [PART MASTER].[REF #], then by [OPEN JOB
RELEASES].[DUE DATE]). That is because the JOB_INVENTORY function I created
stores values in a static variable based on the values passed to it in the
aforementioned ORDER BY statement. (see below)

Function JOB_INVENTORY(JOB As String, REF As Long, QTY As Long, CAP As Long)
As Long

Static REFVARIABLE As Long
Static QTYVARIABLE As Long

If IsNull(JOB) Or JOB = "" Then
QTYVARIABLE = 0
JOB_INVENTORY = 0
Else


If REF <> REFVARIABLE Then
REFVARIABLE = REF
If QTY >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTY - CAP
Else
JOB_INVENTORY = QTY
QTYVARIABLE = 0
End If
Else
If QTYVARIABLE >= CAP Then
JOB_INVENTORY = CAP
QTYVARIABLE = QTYVARIABLE - CAP
Else
JOB_INVENTORY = QTYVARIABLE
QTYVARIABLE = 0
End If
End If

End If
End Function

The problem is that I need to print out the results of the query in a report
that is sorted by [OPEN JOB RELEASES].[DUE DATE] only. I can create this
sorting in datasheet view of the query with noproblem, but, in the report,
the sorting causes the value of release_inv to come out differently.

It appears that the report recalculates the entire query when it applies
it's own sorting. Is there a way to control this behavior? The only thing I
can think of is to change the query to a MakeTable and base the report on the
resultant table. This will work, but it seems like a clunky solution.

Does anyone have any better ideas?

Thanks,

Keith
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

report not showing duplicate records 2
Summing Groups in Reports 2
Increment a number based on instances in another table 1
how do you show all fields from linked data? only shows first one 1
Formula too long 3
How to Define the Print Job Name in Print Queue for a Access Report? 3
Problem with Report Totals 3
Excel To Access 1

Top