Access Query - Create Crosstab?

kwarden13
kwarden13 used Ask the Experts™
on
How can I create a query to show spend and revenue by vendor? I have the following in the database attached.

1) tbl_spend - spend by supplier
2) tbl_rev - revenue by supplier
3) other vendor tables

I need to have the following layout attached.
example_qry.xlsx
sample_3.241.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
What is the difference between tbl_spend and tbl_rev?

To make a crosstab from multiple tables, I usually create a select query first to select the columns and join the tables.  Then I use the crosstab wizard to build the crosstab.  The wizard limits you to three Row Heading fields, so just pick the first three.  Then open the query in design view and add the other five.  Make sure you set the Crosstab line to "Row Heading" when you add these columns manually.  The query also allows you to create column header values that the crosstab will use for the "Value" field.

Author

Commented:
The database I have been providing is just an example. My actual db has a spend and revenue we are managing for vendors and clients.

Much more complicated. I am using the example to try and build out my actual processes and data. I can create the crosstab but only for spend and then one for revenue. Do I need to do a union to put the data together?
Look at sample (qryCross)
sample_3.241.accdb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi als315- How would I create the same for rev and join the two crosstabs?
Distinguished Expert 2017

Commented:
You never explained rev and spend or why you used two tables rather than one with a type code.

Author

Commented:
I thought it would be easier since i get certain attributes from a spend system and certain attributes from our revenue system.

Also spend is updated at a separate time then revenue. Not every vendor has revenue and not every client has spend.
Distinguished Expert 2017
Commented:
But if you want to use them together and sum them, it will be easier to keep them in the same table wit a flag field that identifies which is which.  There is no way to join these two tables since they are not related to each other.

Think of it like this.  You have a table of people and instead of making a table of pets, you make separate tables for cats and dogs.  Although both tables are related to people, they don't have anything that allows you to join them together so if you want to see them in the same report, you either must use a union query or two separate subreports.

Author

Commented:
So i have vendors and client names in the same table but separated the revenue and spend. So I wll try to put revenue and spend in the same table.
You can do it, but in this case you should manually change resulting query every time when year range will be changed. Look at sample (qryRevSpent). It is task for pivot table, but it is deprecated in Access 2016, so you can export or link data to Excel and do it there.
sample_3.241.accdb
Distinguished Expert 2017

Commented:
Hard-coding parameters in queries is poor practice if the parameters can change.  Use a form to run the query and add a control to the form to control the date range.  It is OK to hard-code unchanging stuff.  So for example, if you had a report that you ran that was only ever supposed to include active customers then hard-coding the criteria needed to select "Active" would be fine.  However, if you wanted to use the report for Active, Inactive, or all, then you should make the criteria variable and use a form.  I don't ever rely on prompts since they don't allow for pre-validation which you probably want to do, and you will get prompted at least one more time if you go from print preview to print.

Author

Commented:
Thanks again, Pat! Good advice on the last bullet and I will keep in mind for the future. For now, I will be combining the clients and vendors into one table as suggested and added a field to identify either.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial