Create Report that looks like a spreadsheet

[Hank153]

I'm trying to create a cemetery database in access 2003. The report we want should show the cemetery divided into Section (15), Rows (40) and Plots (1-70 in each row). Each section has 15 rows with 15 plots in each row. I have tables and a query set up with the Name, Section, Row and Plot of each grave. I would like the report to show a grid like sheet with a deceased name in the proper "cell". There should be plot numbers above each column and row numbers at each row.



I'm sort of new at this and not much good with code. I appreciate any help and suggestions anyone can offer.

Thanks, Hank153

[June7]

Data like:

Name	Section	Row	Plot
Jones	A	1	1
Casey	A	1	2
Mason	B	1	1

Try a crosstab query:
TRANSFORM First(Table1.Name) AS FirstOfName
SELECT Table1.Section, Table1.Row
FROM Table1
GROUP BY Table1.Section, Table1.Row
PIVOT Table1.Plot;

The result is:

Section	Row	1	2
A	1	Jones	Casey
B	1	Mason

[Hank153]

Looks like the results I need, but I'm not sure where the code should go. Can I use access 2003 crosstab wizard?

[June7]

Yes, use the wizard.

[Hank153]

Thanks, it works ok now --except -- it will not show empty cells. I think I need to use the NZ function, but I'm not sure just how to use it and where it should go.

[Hank153]

Sorry, I intended to include the code I'm using now. Can you show me where the NZ function should go?

TRANSFORM First(tblNames.FullName) AS FirstofFullName
SELECT tblNames.Section, tblNames.Row, First(tblNames.FullName) AS TotalOfFullName
FROM tblNames
GROUP BY tblNames.Section, tblNames.Row
PIVOT tblNames.Plot;

[June7]

Don't think Nz will help here. What do you mean by 'empty cells'? Do you have a record for every section/row/plot even if the plot is not occupied? If not, can't show data for records that don't exist. If you want to show all plots even if not occupied, need a record for every section/row/plot even if not occupied. Could show 'Not Used' (or whatever) instead of name or just leave it blank.

If you do have a record for every plot then don't understand the issue. All plots should show in the crosstab even if the name field is blank.

[Hank153]

By empty I am saying either nobody is buried there or we have not identified who is there. My preference is to leave the cell blank - but to show the Section, row and plot cell on the report.

[June7]

Does table have records for every section/row/plot?

Not sure how many records that would be because you seem to show contradictory numbers in first post.

[Hank153]

There are 500 - 800 graves. At present I only have 520 names of people buried there. It's an old cemetery (early 1800's) with many unidentified graves. We have divided it into 15 sections with 14 rows - each row contains 15 plots(or graves) or 210 graves in each section. We are able to show one section on each page of the report - using legal size paper. The table does not have a record for every plot. But, I do need to show empty, unknown, or available plots. More sections may need to be added later. For now I'm only working with the 15 sections. You can see that leaves a lot of plots that are not identified at present.
I know I could use the words "Empty" or "Unknown" to show those plots - but I prefer to have the left blank on the report.

[June7]

Okay, then the simplest solution for you may be to create a record for EVERY plot. If name unidentified, leave field empty. Then try the crosstab.