Dynamically Add Columns to a Query or Table

[music_al]

Hi,

I am just using this as a simple example for something more complex (relating to pension providers) that I want to model.

I have a number of products, lets (for ease) call them...

Bucket
Hammer
Screwdriver

I can buy those products from one or more suppliers. Each supplier can supply me with all of these products and they way I record that price is by a percentage above a base price.

So, lets say the base price for the products are...

Bucket - £10
Hammer - £20
Screwdriver - £30

Each supplier has a FIXED percentage uplift for all products.

Supplier A = 10%
Supplier B = 20%
Supplier C = 30%

Id like to produce a table or query which shows my products down the left (in rows) and across the top (in columns) Id like to see the base price and a column for each supplier from my Suppliers table. The data in the supplier columns will be the cost of the product - base cost x supplier percentage uplift. This should be calculated automatically.

As I add a new supplier Id like a new column to be automatically added to the query/table. I don't want to have to manually add these columns

How do I do this ?

 

[isladogs]

As described, you will be creating an un-normalised table. Not a good idea.

Create a new record when you get a new supplier (not a new field).
Use a crosstab query to view a spreadsheet format output with suppliers in separate columns.
OR do the whole thing in Excel instead

 

[Ranman256]

usu a query has known # columns.
Supplier, Product, Price

adding a new name to the record, does not add a field to the columns.
data flows vertically

you may want a crosstab query for your need.

 

[Pat Hartman]

What are you actually going to do with this query? The others have told you that it is far better to normalize your data. That means three tables

tblProducts
ProductID (autonumber PK)
ProductDesc
BasePrice

tblSuppliers
SupplierID (autonumber PK)
SupplierName
UpliftPct

tblProductSupplier
SupplierProductID (autonumber PK)
SupplierID
ProductID

It is this third table which is frequently referred to as a junction table, makes the many-many relationship between products and suppliers

Once the third table is created, you can use a crosstab query to create the view you are asking for.

 

[music_al]

See my last paragraph in my original post...

Id like to produce a table or query which shows my products down the left (in rows) and across the top (in columns) Id like to see the base price and a column for each supplier from my Suppliers table. The data in the supplier columns will be the cost of the product - base cost x supplier percentage uplift. This should be calculated automatically.

As I add a new supplier Id like a new column to be automatically added to the query/table. I don't want to have to manually add these columns

 

[Minty]

As the other replies have stated, a cross tab query will do what you want assuming your data is stored correctly, no need for any manual additions.

I would suggest you have a play with the query to give you your "vertical" data, then change it to a crosstab , and play with the various option.

If you get stuck post up a sample set of data in a zipped database and someone will be sure to assist.

 

[Frothingslosh]

See my last paragraph in my original post...

And they're telling you that your database structure is bad, and bad in such a way that you're going to find yourself having more and more and more problems as time goes on.

The structure Pat provided will handle everything you want, allows for easy adding of new suppliers, and will save you dozens to hundreds of hours down the road.

If you need to see suppliers as columns, that's why we have crosstab queries.

 

[music_al]

Ive maybe made it complicated by giving a bad example. The products are pensions that an employee can pay into. There are 3 pension providers, lets say they are provider A, B and C.

Each pension PRODUCT is a percentage of the employees annual salary, 10%, 15% and 20% respectively. The percentage is recorded in the PENSION_PROVIDER table.

I want to show these values in a table that shows the salaries of particular grades (grades of roles, e.g. Project Manager Grade 7). We're not really interested in the role - just the grade.

See the attached.

 

[plog]

Perhaps I, the 6th person to say this will be the one to get through:

You need to structure your data properly in your table(s). This process is called normalization (https://en.wikipedia.org/wiki/Database_normalization).

A proper table structure doesn't expand and contract with more/less fields. It's set up to accomodate data vertically by adding new rows of data. Your data is not an exception to this. It should be stored properly. I suggest you read up on normalization and then when you want your data out of your database you either use Excel Pivot tables or Access Cross tab queries.

 

[Frothingslosh]

That still really doesn't change the fact that your data structure as you described it is horribly denormalized, and your problems resulting from it (like this one) are just going to get worse and worse over time.

Basically, you're thinking in terms of spreadsheets, and Access just doesn't save data the same way. You HAVE to store those providers in a Providers table. For display purposes, you then use a crosstab query to create the grid format you're asking for.

Think about it - if you keep adding a new column every time you add another provider, how do you handle further growth? What about every query, report, and form involving that data? Every time you add a new column, you will need to go back through every single item that touches that table and change them, costing you a LOT of man-hours.

Or you can follow our advice, properly normalize the data, use a crosstab query for what you're trying to display, and the database will maintain itself as providers are added.

EDIT: Aaaaand plog totally beat me to it.

 

[music_al]

I'm not a novice to database design but I've never created a Crosstab query before.

These products are just a percentage of an employees wage - that's it. Effectively, its just one product from each pension provider.

I have a table of Pension Providers which just has 3 fields
Pension Provider ID
Pension Provider Name
Pension Percentage

I have a table that shows the salaries for each grade
Grade ID
Grade
Annual Salary

So, when a new Pension Provider is added, it will show in the query as a new column and the cost of that pension is shown next to each grade.

I understand when to use intermediate / junction tables but I don't understand why I would have to use one here.

 

[Frothingslosh]

Here, perhaps this will help:

http://allenbrowne.com/ser-67.html

With Access, you don't even need to worry about the SQL, since the query builder has a crosstab option in the same area of the ribbon where you'd find update, delete, and append queries.

Edit: Also, we strongly advise against putting spaces in field names. They just make dealing with the fields much, much harder.

 

[music_al]

None of my field names have spaces - I just wrote them like that above just for speed.

 

[Frothingslosh]

None of my field names have spaces - I just wrote them like that above just for speed.

Fair enough. You would not BELIEVE how often we run across spaces.

 

[Pat Hartman]

If you can post your database with some data (obfuscate anything sensitive), it would help us to help you.