Combining Lines for a report

[Shipper225]

I am using Access 2007 and am attempting to create a report similar to one I currently use in Excel. The issue I seem to be having is finding a way to have the data for each 'job' on the same line going from left to right lining up with the days of the month. I've attached a PDF of what I have and what I am trying to recreate. Any ideas would be helpful. Page one is what I have, page two is what I am hoping to replicate. As the attachment shows I am able to get the info in the report but for each day there is a shipment it creates a new line, I am hoping to combine all the same job shipments on one line to keep the report as short as possible.

 

[BBQ Kittens]

hmm i do not see your attachment, see if you can post that pdf and if possible the database or at least the structure so we can see what we are working with. If you have structured correctly then this can be done, either by a cross tab query or by a few creative queries to reorganize the appearance.

 

[Shipper225]

Seems the attachment didn't work the first time, reattached. It will take a little bit for me to extract the tables/query before I can attach that.

 

[Shipper225]

Attached is the report and related tables. I am using 11/1/11 and 11/30/11 for the pop ups since to attach that form would have made the database even bigger.

 

[vbaInet]

I don't see why you're trying to replicate an Excel report in an Access environment. You will have difficulty replicating that report.

The first one is a crosstab and the second is a pivot table.

 

[Shipper225]

My department is trying to get away from using Excel reports as Access fills in the data we need and is much easier to generate reports. I've got the lay out but I wasn't sure if it was possible to replicate the design, figured I'd put it out there since someone said almost anything is possible using Access.

I considered dumping the dates into a table and having a query pull the records I needed but I wasn't sure how to make an append query take the proper information from the tblDepthRecord table and put it into the new one, I also considered using a SubReport but realized I'd most likely need one for each day of the month and that would slow things down greatly.

 

[vbaInet]

Almost anything is possible in Access if you're an expert. It will take a considerable amount of time to replicate the spreadsheet.

So like I mentioned, the first one is crosstab report, that should be easy enough. And the second one you need a pivot table, not easy. This is also dependent on how your tables are setup as well.

 

[vbaInet]

Almost anything is possible in Access if you're an expert. It will take a considerable amount of time to replicate the spreadsheet.

So like I mentioned, the first one is crosstab report, that should be easy enough. And the second one you need a pivot table, not easy. This is also dependent on how your tables are setup as well.