set variable to ms access query

Question

I have a MS Access database which consists of 9 tables. Each one represents landfills that collect garbage. These tables contain several fields, the primary key being the date and the rest of the fields are the tonnage of garbage that are send to each landfill by several municipalities. For example for the table landfill 1:

date municipality 1 municipality 2

1/1/2014 50 tons 30 tons

The query I'm trying to create is find out how many tons of garbage each municipality sends to all the landfills at a specific date. The code should be sth like

SELECT Landfill_1.municipality_1,    Landfill_2.municipality_1,...,Landfill_10.municipality_1
FROM Landfill_1.date INNER JOIN Landfill_2.date ,..., INNER JOIN Landfill_10.date
WHERE Landfill_1.date=[Please select the date:];

My problem is that municipality 1 should be a variable so that I don't have to change it each time.

You mean you want to change the name of municipality_1 field? — Maciej Los
– Maciej Los, Commented Mar 4, 2015 at 20:51
Yes, I mean if someone wants to use the same query for municipality_2 he shouldn't manually change it for each Landfill table (10 times)! — 5356aragorn
– 5356aragorn, Commented Mar 4, 2015 at 20:59

Pekka · Accepted Answer · 2015-03-04 23:09:46Z

3

Unpivot the source data. Record in each row only a single set of variables and incorporate the municipality and landfill identifiers into the key.

Landfill, municipality, date,     amount
1,        1,            1/1/2014, 50
2,        1,            1/1/2014, 30

Use the query

SELECT Landfill, municipality, date, SUM(amount)
    GROUP BY Landfill, municipality, date

to return the aggregate results and then pivot the result in either Access or in Excel.

Avoid data that is not normalised in the database to permit more general queries. The presentation of the results should not be the primary consideration for the database schema design.

This also extends to any number of landfills and municipalities with no further database schema changes.

edited Mar 4, 2015 at 23:09

answered Mar 4, 2015 at 22:55

Pekka

3,67431 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

5356aragorn Over a year ago

Is there no other way? I don't see this solution as viable because I take the data from excel worksheets which allows me to easily copy and paste them to my database, if i follow your solution I'll have to manually type the data for about 10000 entries.

Pekka Over a year ago

If the data is already captured in a fixed form, you can create an interim table to move it into Access. Then, use an INSERT query to move the data into the correct form. Ask that future data is collected in the correct form and provide PivotTables to the users in their workbooks so that they can continue to view the data in their preferred format.

Gustav · Accepted Answer · 2015-03-04 21:21:11Z

0

Append data from all tables to one single table with the added field: MunicipalityId. Fill this with 1 to 9 as from which table the record origins.

Use this field and the date field in a compound unique index.

Now you need one query only.

answered Mar 4, 2015 at 21:21

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

set variable to ms access query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related