V
Victic31
Hi,
Wasn't sure whether to post this in "Queries" or "Reports" so I opted for
"General Q". Apologies if wrong.
I'm having a problem with a report. Well it is more like 2 queries and a
report.
The issue is that when I put a calculated field in the "Report Footer" I
loose half the data.
Here is an outline of what I am doing (these aren't the actual fieldnames, I
have used fieldnames here that I hope are self explanatory for format & data
type etc).
The data fields in the report are as follows:
VehicleRegistrationNumber, InspectionDate, OdometerKMS, PriorDate, PriorKMS,
ElapsedDays, ElapsedKMS.
These fields are sourced from 2 stand alone queries, as follows:
Query 1
selects VehicleRegstrationNumber, InspectionDate & OdometerKMS from a
MaintenanceRecords table for all records that match a particular Vehicle Reg
Number, and which have a Inspection flag = True.
Query 2
selects the results from Query 1 and by means of 2 sub-queries
produces 2 new fields "PriorDate" and "PriorKMS".
(This is the InspDate and OdoKMS from the immediately preceeding record)
(many thanks to Karl Dewey & John Spencer for their help here)
I have then added 2 more fields "ElapsedDays" & "ElapsedKMS" which are 2
simple calculatons "[InspectionDate]-[PriorDate]" and
"[OdometerKMS]-[PriorKMS]".
Up to this point everything works as it should.
I then created a report using "Query2" as the source data. For some reason
this did not work and I got a message about "Multi-Level Group By" not being
allowed in subquery.
So, I used the query builder from within he report designer and used
"Query2" as the source for this query and everything seemed to work ok.
So far so good. I then put a calculation field into the "Report Footer" of
the report. Nothing grand, just a count of the records being listed. The
formula is "=Count([VehicleRegistrationNumber]).
Now when I tested this it worked and returned the correct value for the
record lines listed.
BUT..... in doing this the "PriorDate", "PriorKMS", "ElapsedDays" &
"ElapsedKMS" data disappears from the report.
If I delete the field in the "Report Footer" the data re-appears. Put the
field back into the footer and it dissappears.
No doubt this is something insanely stupid that I am doing wrong, however
any suggestions (other than "Don't give up your day job!") would be
gratefully received.
Thanks in advance
regards
Victor
"Intermediate Level" Access User
&
"Absolute Novice" SQL user
ps.
For those of your wondering why I have 2 standalone queries.
The data for first query comes from 2 related tables. The
"VehicleRegistrationNumber" comes from the "Vehicles" table, and the
"InspectionDate" from the "MaintenanceRecords" table.
I couldn't figure out how to write the SQL for the sub-queries when 2 tables
are involved, so decided to pull this data into the first query and use a 2nd
query to do the sub-query work.
Wasn't sure whether to post this in "Queries" or "Reports" so I opted for
"General Q". Apologies if wrong.
I'm having a problem with a report. Well it is more like 2 queries and a
report.
The issue is that when I put a calculated field in the "Report Footer" I
loose half the data.
Here is an outline of what I am doing (these aren't the actual fieldnames, I
have used fieldnames here that I hope are self explanatory for format & data
type etc).
The data fields in the report are as follows:
VehicleRegistrationNumber, InspectionDate, OdometerKMS, PriorDate, PriorKMS,
ElapsedDays, ElapsedKMS.
These fields are sourced from 2 stand alone queries, as follows:
Query 1
selects VehicleRegstrationNumber, InspectionDate & OdometerKMS from a
MaintenanceRecords table for all records that match a particular Vehicle Reg
Number, and which have a Inspection flag = True.
Query 2
selects the results from Query 1 and by means of 2 sub-queries
produces 2 new fields "PriorDate" and "PriorKMS".
(This is the InspDate and OdoKMS from the immediately preceeding record)
(many thanks to Karl Dewey & John Spencer for their help here)
I have then added 2 more fields "ElapsedDays" & "ElapsedKMS" which are 2
simple calculatons "[InspectionDate]-[PriorDate]" and
"[OdometerKMS]-[PriorKMS]".
Up to this point everything works as it should.
I then created a report using "Query2" as the source data. For some reason
this did not work and I got a message about "Multi-Level Group By" not being
allowed in subquery.
So, I used the query builder from within he report designer and used
"Query2" as the source for this query and everything seemed to work ok.
So far so good. I then put a calculation field into the "Report Footer" of
the report. Nothing grand, just a count of the records being listed. The
formula is "=Count([VehicleRegistrationNumber]).
Now when I tested this it worked and returned the correct value for the
record lines listed.
BUT..... in doing this the "PriorDate", "PriorKMS", "ElapsedDays" &
"ElapsedKMS" data disappears from the report.
If I delete the field in the "Report Footer" the data re-appears. Put the
field back into the footer and it dissappears.
No doubt this is something insanely stupid that I am doing wrong, however
any suggestions (other than "Don't give up your day job!") would be
gratefully received.
Thanks in advance
regards
Victor
"Intermediate Level" Access User
&
"Absolute Novice" SQL user
ps.
For those of your wondering why I have 2 standalone queries.
The data for first query comes from 2 related tables. The
"VehicleRegistrationNumber" comes from the "Vehicles" table, and the
"InspectionDate" from the "MaintenanceRecords" table.
I couldn't figure out how to write the SQL for the sub-queries when 2 tables
are involved, so decided to pull this data into the first query and use a 2nd
query to do the sub-query work.