Data in columns

  • Thread starter Thread starter Fernando Oliveira
  • Start date Start date
F

Fernando Oliveira

Good afternoon,

In Access 2007, I have a table that looks like this:

EquipID Text (5)
ChackDate Date
UserID Text(2)
Check1 Y/N
Check2 Y/N
Check3 Y/N
Obs Text (255)
....


I need a report that looks like this:

01/01/2007
Equipment A1 A2 A3
User MF FO MF
Bolts secured Y N Y
Instruction Manual Y Y N
Packaging Y Y Y
Obs XPTO N.G.
....

The report has to be printed landscape because of the check point/Nr of
equipments checked ratio.

I tryed to make a main report with the row headings with a subreport with 10
columns defined in the pages properties, but the sub report only creates the
columns if I run it alone. Inserted into the main report, it only shows a
column.

I tryed to put the row headings in a group (date) with a new column after
the section. This brought me closer to the layout that I need but the problem
is that since the headings are wider (6cm) than the Y/N fields (2cm) , it
creates the columns with the same width (6cm) as the row heading causing a
big waste of space.

Any ideas are welkomed.

Best regards,
Fernando Oliveira
 
It would help if you provided a table name other than "Looks Like This". You
also didn't mention anything about a primary key field or fields or what
combination of fields might be unique.

Your table isn't normalized so first normalize your data with a union query
[quniChecks]
SELECT LooksLikeThis.ChackDate, LooksLikeThis.EquipID,
"Bolts secured" AS Checked, IIf([Check1],"Y","N") AS Result
FROM LooksLikeThis
UNION ALL
SELECT LooksLikeThis.ChackDate, LooksLikeThis.EquipID, "User", UserID
FROM LooksLikeThis
UNION ALL
SELECT LooksLikeThis.ChackDate, LooksLikeThis.EquipID, "Instruction Manual",
IIf([Check2],"Y","N")
FROM LooksLikeThis
UNION ALL
SELECT LooksLikeThis.ChackDate, LooksLikeThis.EquipID, "Packaging",
IIf([Check3],"Y","N")
FROM LooksLikeThis
UNION ALL SELECT LooksLikeThis.ChackDate, LooksLikeThis.EquipID, "Obs", [Obs]
FROM LooksLikeThis;

Then create a crosstab with a SQL view of:
TRANSFORM First(quniChecks.Result) AS FirstOfResult
SELECT quniChecks.Checked
FROM quniChecks
GROUP BY quniChecks.Checked
PIVOT Format([ChackDate],"mm/dd/yyyy") & [EquipID];
 
Back
Top