I'm trying to create a simple database to log maintenance activity at various sites as well as some very basic numerical information collected from each visit. Each site is known only by its numerical X, Y coordinates, and some have descriptions. There will be a small number of visits logged for each site (1-10), and the list of sites will increase or decrease as needed.
Fields: [xCoord],[yCoord],[siteDescription],[visitDate],[visitTime],[data1],[data2],[data3],[data4],[data5]
I started a table with columns for each of the above pieces of information, and a form for easy entering of new data, which is working great. Now I've got a few hundred records, and want to create a list of the data, I'm running into problems formatting the data.
I would like to see a list of sites (grouped by x, y coords) without duplicates. Broken down beside each site should be a list of the maintenance data, sorted by date.
What type of Object should I use to display information in this way? I can create a query that groups identical X and Y values together, but then it won't give a breakdown of individual records.
I've tried a Crosstab with some success, but because it's limited to only a single column header, and I have at least 5 columns of data, I need a separate crosstab for each column of data, which is cumbersome.
Anyone know a trick to get this done?