Crosstab Query

[Accessbeginner34]

Hi - I am trying to create a crosstab query to attempt to replicate a Pivot Table in Excel. I want the cross tab to look like this

Work Description Condition Code Cost of Work
Building 1 Replace windows B £20,000
Building 2 Redecorate C £10,000

All the information I need is in a table in Access (2016 Professional Plus) and I have used the Query Wizard to try and run a cross tab. I can add the row description as Building but it will only let me pick one column heading. e.g Building and Work Description or Building and Condition Code. The instruction's seem to suggest that more than one column can be added but I can't seem to work out how.

Any ideas?

Thanks

 

[plog]

Why do you need a cross tab? A cross tab turns the values in one field into column headers. None of the expected column headers look like they would be values in a field. Further, you can only turn 1 field into column headers, so what you want may not be possible even if you are turning one field into column headers

We know what you want to end up with, now show us the table(s) your data is in. Include table and field names and sample data that creates the expected results.

 

[arnelgp]

I think its more of a Total query for me.

 

[Accessbeginner34]

I have perhaps misunderstood how cross tab works. I thought this was like a Pivot Table in Excel.

Okay so the table the data comes from is much bigger and the column headings in A - Zare as follows:

Site,
Building, Building 1, 2, 3, 4 etc
Building Residential,
Location Parent,
Location Parent Residential,
Work Description,
Surveyors Notes,
Survey Location Residential,
Element Code,
Element Name,
Element Description Ref,
Element Description Name,
Comments,
Condition Code,
Condition Description,
Condition Code Risk Score,
Priority Code,
Priority Description,
Priority Code Risk Score,
Risk Likelihood Description,
Risk Likelihood Score,
Risk Consequence Description,
Risk Consequence Score,
Overall Score,
Start Year,
Total Cost

 

[The_Doc_Man]

The problem I see here is that since you are trying to replicate something you can do in Excel, you kept the data as you would have kept it in Excel. Which for Access is the wrong approach.

What you need is a parent/child setup. You have your buildings and things that are uniquely related to that building. But the WORK done on the building would go into a separate table where the Building ID would be the Prime Key of the building table but a Foreign Key in the Work table. I could imagine that the work's Code, Description, and Cost would be elements of the Work table. You do it that way because, of course, you can do more than one type of work on the same building in the same year, right?

Once you have that, you can create a report where each building's work is gathered together (GROUP BY type of thing) and the work items are detailed line items in the Detail section under the grouping.

Since you took this approach, my FIRST suggestion has to be for you to read up on database normalization. In this forum, the SEARCH feature is 3rd from the right in the thin ribbon near the top of the page and you can search for "normalization." If you search the web, look for "database normalization" (because there are other kinds of normalization such as political and mathematical). Limit yourself in that case to articles that were published by colleges or university in the .EDU domain, at least at first.

 

[plog]

So now we are ditching the issue you initially posted about and focusing on the bigger problem--you haven't structured your database correctly. The process is called normalization (https://en.wikipedia.org/wiki/Database_normalization). Read through that, find a few tutorials and then apply it to your database.

Databases aren't spreadsheets, they operate and store data completely different. The goal of databases is to store data vertically (with more rows) and not horizontally (with more columns). You have too many columns.

Using just a subset:

Condition Description
Condition Risk Score
Risk Consequence Description
Risk Consequence Score
Risk Likelihood Description
Risk Likelihood Score
Priority Description
Priority Code Risk Score

Instead of those 8 fields, you need a new table that has just 5:

tblRiskScores
rs_ID, autonumber primary key
ID_Site, number, foreign key to SiteID which should be an autonumber primary key in the table you listed
RiskCategory, text, this will represent the type which is now in the field name
RiskDescription, this will hold what's in the current [xxx Description] fields
RiskScore, this will hold what's in the current [xxx Risk Score] fields

That's it, those 5 fields will hold all the risk score/description data (including some fields I left out). That is how a database is to be structured. Again, read up on normalization, leave this current issue aside and fix your tables.

 

[Accessbeginner34]

Great thanks for your help. I will look at this information and see how I get on.

Thanks