Display all results for object on 1 line.

[Rik_StHelens]

I have a query which pulls the dates of all jobs performed on a customers vehicles.

Currently we create a summary report in excel which shows in which of the last 12 months their vehicles were inspected, which takes a long time.

Currently my query will look at the inspection date month, and put checked under the corresponding month in the results. I would like it to look like this
Eg:

Reg Jan Feb Mar Apr May Jun etc
AB1 Checked Checked Checked Checked
AB2 Checked Checked Checked
AB3 Checked Checked Checked

But at present it looks like this:

Reg Jan Feb Mar Apr May Jun etc
AB1 Checked
AB1 Checked
AB1 Checked
AB1 Checked
AB2 Checked
AB2 Checked
AB2 Checked


Now i know that it is doing it like this because it creates a new line every time it finds a new instance of that Reg being checked in the table.

how would i recreate the desired format as shown in my first example?

here is my current sql:

SELECT tblVehicle.Customer, tblVehicle.FleetName, tblVehicle.RegistrationNumber, IIf(Month([InspectionDate])="1","Checked","") AS Jan, IIf(Month([InspectionDate])="2","Checked","") AS Feb, IIf(Month([InspectionDate])="3","Checked","") AS Mar, IIf(Month([InspectionDate])="4","Checked","") AS Apr, IIf(Month([InspectionDate])="5","Checked","") AS May, IIf(Month([InspectionDate])="6","Checked","") AS Jun, IIf(Month([InspectionDate])="7","Checked","") AS Jul, IIf(Month([InspectionDate])="8","Checked","") AS Aug, IIf(Month([InspectionDate])="9","Checked","") AS Sep, IIf(Month([InspectionDate])="10","Checked","") AS Oct, IIf(Month([InspectionDate])="11","Checked","") AS Nov, IIf(Month([InspectionDate])="12","Checked","") AS [Dec]
FROM tblVehicle LEFT JOIN tblInspection ON tblVehicle.RegistrationNumber = tblInspection.Registration
WHERE (((tblVehicle.Customer) Like "*" & [Customer Name] & "*") AND ((tblInspection.InspectionDate)>Date()-365));


Thanks for your help

 

[Rik_StHelens]

damn,

it removed my formatting on my examples.

never mind i'm sure you'll get the picture of what it is im after.

 

[DCrake]

Have a look at crosstab queries.

 

[Rik_StHelens]

I did try a cross tab but it still displays a new line for each check it finds on a vehicle, rather than combining them into one line, and it also then displays all job dates across the whole range of results as column headings.

Thats why i went with the query that gave the least messy results when i posted on here.

My crosstab spl is as follows:

PARAMETERS [Customer Name] Text ( 255 );
TRANSFORM Sum(tblInspection.[InspectionDate]) AS [No of Inspections]
SELECT tblVehicle.RegistrationNumber, tblVehicle.FleetName, IIf(Month([InspectionDate])="1","Checked","") AS Jan, IIf(Month([InspectionDate])="2","Checked","") AS Feb, IIf(Month([InspectionDate])="3","Checked","") AS Mar, IIf(Month([InspectionDate])="4","Checked","") AS Apr, IIf(Month([InspectionDate])="5","Checked","") AS May, IIf(Month([InspectionDate])="6","Checked","") AS Jun, IIf(Month([InspectionDate])="7","Checked","") AS Jul, IIf(Month([InspectionDate])="8","Checked","") AS Aug, IIf(Month([InspectionDate])="9","Checked","") AS Sep, IIf(Month([InspectionDate])="10","Checked","") AS Oct, IIf(Month([InspectionDate])="11","Checked","") AS Nov, IIf(Month([InspectionDate])="12","Checked","") AS [Dec]
FROM tblVehicle LEFT JOIN tblInspection ON tblVehicle.RegistrationNumber = tblInspection.Registration
WHERE (((tblVehicle.Customer) Like "*" & [Customer Name] & "*") AND ((tblInspection.InspectionDate)>Date()-365))
GROUP BY tblVehicle.RegistrationNumber, tblVehicle.Customer, tblVehicle.FleetName, IIf(Month([InspectionDate])="1","Checked",""), IIf(Month([InspectionDate])="2","Checked",""), IIf(Month([InspectionDate])="3","Checked",""), IIf(Month([InspectionDate])="4","Checked",""), IIf(Month([InspectionDate])="5","Checked",""), IIf(Month([InspectionDate])="6","Checked",""), IIf(Month([InspectionDate])="7","Checked",""), IIf(Month([InspectionDate])="8","Checked",""), IIf(Month([InspectionDate])="9","Checked",""), IIf(Month([InspectionDate])="10","Checked",""), IIf(Month([InspectionDate])="11","Checked",""), IIf(Month([InspectionDate])="12","Checked","")
PIVOT tblInspection.InspectionDate;


But i don't know if that will shed any light on what im doing wrong/need to do to fix it?

 

[DCrake]

How is your table sttructured that captures the monthly checks?

Can you provide a excel layout of what you get/want for brevity

David

 

[Rik_StHelens]

No problem.

I will get to work on it after lunch, and post it online

 

[Rik_StHelens]

The inspections table is used as follows

Every time we get a fleet inspection we create a new record which is given an InspectionID (autonumber), and the registration number of the vehicle links to the vehicle table to give us a customer name, fleet name, fleet location and the dedicated dealer who would carry out the inspection. We also add in an inspection date, and after this we just have details about each tyre position on the vehicle, tread depth, unever wear, inspector recommendations etc, although this detail is not required in this query.

I have also attached a non 2007 excel file with the layout i want/am getting at present.

Thank you for your help. It really is appreciated. I'm competent with access but, when something throws me completely and i can't find an answer researching the net, i get stuck in a rut. But i'm learning every day (mainly thanks to this forum!)

Thanks again

 

[DCrake]

If you take out the inspection date from the crosstab query and replace it with

Total:Count(1)

And make this an expression I think it should work. Because there are more than one inspection dates it it adding it as a group as well. By uning an expression to count removed that ideology but you should still get a total.

 

[Rik_StHelens]

I tried as you suggested but it just comes back with an error "too many fields defined".

As we have to pull results back from only the last year i will also rewrite

Jan: IIf(Month([InspectionDate])="1","Checked","")

to

Jan: IIf(Month([InspectionDate])="1" AND [Inspectiondate]>Date(),"Checked","")

and this should (i hope) just record dates within the last year.

 

[DCrake]

Can you send me a sample table/ query to look at?

David

 

[Rik_StHelens]

Can you send me a sample table/ query to look at?

David

the customer name i use is "cpl"

i've scaled it down to just the essential tables.

i didn't build the initial db that this takes its data from, so table structure etc was not of my doing

 

[Rik_StHelens]

just wondered if anyone had had a chance to look at this and come up with any ideas?

Wasn't in the office yesterday so i couldn't check.

Thanks for your time.

 

[Rik_StHelens]

I've had a new idea with regards to this one which ALMOST works.

I decided to create a report for the query, which groups by fleet, and then registration.

In the detail section, the individual records for a check on a gvehicle are shown descending 1 after the other (as in the attached excel spreadsheet in a previous post)

Then in the registration footer, I have a series of text boxes with the following formula

=iif([Jan]="Checked", "Checked", "") and so on for each month

in print view i suppressed the detail section so it would just show the reg footer summary.

However, the footer only looks at the last record in a grouping.

Please see the attachment for details of what is currently displayed, and should be displayed.

How can i get the footer to summarise the whole group of records?

Thanks for your help

 

[Rik_StHelens]

Another new idea!

I realised that a vehicle can only be checked once a month, maximum. And, as we are only pulling 12 months of data, i thought i'd use an IIF statement to work out where a "checked" was displayed in order to put it in the summary.

For example:

=iif(count([Jan]="*", 1, 0)-1)=(count(iif([Jan]=”Checked”, 0, 1,))), “Checked”, “”)


However it says the statement contains a number of wrong arguments. But the general idea behind it is that if the total number of records for a month, minus one, equals the count of records that do not say "checked", display "checked" in the group footer summary.