Vertical Reporting

[Accessosaurusrex]

I have read through the other posts and am not sure which way to go. I have a table that has 4 fields in it. What I want for a result is to put this in a column format. The four columns are a date, a name and then two numerical field values qty and reject. This data covers a lengthy period and there will be some null values. I want a simple sheet that lists as follows:

........Name..... Name.... Name.... Name ....
Date QTY REJ QTY REJ QTY REJ QTY REJ ....

I have tried the page format column thing but have failed there. Was thinking of running subreports but I have over 50 names so that may not be the most efficient.

Thanks for your time!

 

[Beetle]

Sounds like a job for a crosstab query, except that you can only use one field for the Value column in a crosstab so you may need to create another query that would combine the Qty and Rej fields into one calculated field with something like;

Totals: "Qty (" & Nz([Qty],0) & ") - Rej(" & Nz([Rej],0) & ")"

Then base your crosstab on that query, which might give you results like;

.........................Bill......................Fred....................Sally
3/1/2012...Qty (1) - Rej (2)....Qty (3) - Rej (0)....Qty (0) - Rej (3)
3/2/2012...Qty (2) - Rej (1)....Qty (0) - Rej (1)....Qty (6) - Rej (1)

Might not be ideally what you want, but it's a thought anyway.

 

[Accessosaurusrex]

Well that actually worked for me for now. I think it will be fine right up until someone gets a great idea to start having to manipulate those numbers individually. For the moment that was a perfect solution. I simply did a make table query to export the values needed and then did an expression to get them together and then the crosstab query which got me exactly what I needed. Thank you so much for the advice.

Have a good one!

 

[Beetle]

Happy to help

 

[Accessosaurusrex]

Well surprise. As soon as I got it running that way, one of the end users wanted to change it so the values are separate for other functions. So is there any way to have a 4 field table lay out the data in a vertical column like a cross tab query, or is there some way to merge a pair of cross tab queries to get a final result?

Thanks again and I had hoped we had it solved but just not quite there.

 

[Beetle]

one of the end users wanted to change it so the values are separate for other functions

Not sure what you mean by this. If you mean that you have actual Functions in your application that need to use the individual values, then you would just retrieve the values from the underlying table. If you mean that the user wants to be able to manually update the value, you wouldn't be able to do that in a crosstab query no matter how you set it up (crosstab queries are read only). I don't know of a way to get that type of a layout that would actually be updateable. You would probably need to open another form for updating the values then requery the crosstab after that form is closed.

 

[Accessosaurusrex]

My apologies for being confusing. I have everything working through use of pivot tables in Access 2007. I modified the data so the pivot table would work for it and then simply did a query to clear a table, append to the table and then create a report from the appended table. That seems to be working fine. Thank you again for all your help!