Transposing Headings into records

Resolved · Urgent Priority · Version 2003

Tue 3 May 2011, 16:56 Delegate Andrew said...

Andrew has attended:
Access Introduction course
Access Intermediate course
Access Intermediate course
Access VBA course

Transposing Headings into records

Hi,

I am creating a pricing tool that should enable the user to choose from a list of countries in a form, those for which he needs the prices for the delivery of postal items. These prices will then be processed to add different costs and prices, to finally produce a quote/report that will be sent to the customer/prospect.

Below is a summarised version of how we receive the tariffs (please note that the full rate card comprises over a hundred destinations and weights up to 75kg):

Min Weight Max Weight France Germany Italy Spain
0 gr 1000 gr £12.00 £10.00 £8.00 £19.00
1001 gr 2000 gr £12.53 £11.20 £8.26 £20.85
2001 gr 3000 gr £13.06 £12.40 £8.52 £22.70
3001 gr 4000 gr £13.59 £13.60 £8.78 £24.55
4001 gr 5000 gr £14.12 £14.80 £9.04 £26.40

So what I’d like to have in effect is if a user chooses Germany and Spain for instance in the drop down list, the following table/query (?) is produced:

Min Weight Max Weight Germany Spain
0 gr 1000 gr £10.00 £19.00
1001 gr 2000 gr £11.20 £20.85
2001 gr 3000 gr £12.40 £22.70
3001 gr 4000 gr £13.60 £24.55
4001 gr 5000 gr £14.80 £26.40

The first step for that, I imagine, would be to convert the original table so that the country headings are no longer headings, but variables of one same heading “Countries” as follows:

Min Weight Max Weight Countries Price
0 gr 1000 gr France £12.00
1001 gr 2000 gr France £12.53
2001 gr 3000 gr France £13.06
3001 gr 4000 gr France £13.59
4001 gr 5000 gr France £14.12
0 gr 1000 gr Germany £10.00
1001 gr 2000 gr Germany £11.20
2001 gr 3000 gr Germany £12.40
3001 gr 4000 gr Germany £13.60
4001 gr 5000 gr Germany £14.80
0 gr 1000 gr Italy £8.00
1001 gr 2000 gr Italy £8.26
2001 gr 3000 gr Italy £8.52
3001 gr 4000 gr Italy £8.78
4001 gr 5000 gr Italy £9.04
0 gr 1000 gr Spain £19.00
1001 gr 2000 gr Spain £20.85
2001 gr 3000 gr Spain £22.70
3001 gr 4000 gr Spain £24.55
4001 gr 5000 gr Spain £26.40

Is this correct?

If this is the case, I have looked on the internet and on a few forums to find out how this can be done, but have had no luck.

How could I use a Query/VBA to convert this?

I really could do with some help to point me in the right direction.

Thanks very much in advance for your help.

Wed 4 May 2011, 10:14 Trainer Anthony said...

RE: Transposing Headings into records

Hi Andrew, thanks for your query. Yes, you should create a country field with the relevant names in them, this will make the database a lot easier to hande in the long run, but actually you don't need to do this to achieve the result you need here.

You are only running simple queries from the initial table. Assuming you have the tarrifs as a standalone table as cited above, your query to pull out the Germany and Spain data would simply involve using the query designer to include min weight, max weight, Germany and Spain fields from the tariff table. To give this ability to the user then involves building the form (with check boxes et) and then writing some code to concatenate together the correct SQL string and returning those results. We do something similar to this on the Access VBA course. Depending on what control the user selects (say, from a country dropdown), take the current value and bolt it into the middle of an SQL statement then run that query. If you are going to embark on writing that code, I would suggest first you create a general Country field which, if the tariffs are changing regularly you could populate dynamically using acton queries.

Hope this helps,

Anthony

Tue 10 May 2011: Automatically marked as resolved.

Training courses