table joins and queries

Mark77

Board Regular
Joined
Aug 27, 2004
Messages
66
Hello

I have a database which holds a main table. The main table holds the first part of all UK postcodes. This has three fields, ID, Postcode Outer and Date added.

The second table holds a further ID, the ID number from the main table, the next part of the postcode which is a numeric and again date added.

The third table holds ID, ID from the second table, last two letters of the postcode and date_added

The final table holds ID, ID from the third table, buildings area, contents area and effective date.

Each postcode is given a rating area to allow us to price insurance based on home location. These rating areas change regularly hence the need for an effective date in the fourth table.

I have a query (thanks to Giacomo who's explanation to someone elses query with some adaption allows me to pull the newest rate from the fourth table. The SQL is as follows:

SELECT x.Effective_date, x.Walk_ID, x.Builidngs_Area, x.Contents_Area
FROM tbl_Rating_areas AS x
WHERE (((x.Effective_date)=(select max(effective_date) from tbl_rating_areas where walk_id = x.Walk_ID)));

What I would like to do though is add fields to this query to show information from the first table, second table and third table. However by doing this I then get multiple results from the fourth table showing all results instead of the latest rates.

Please could someone help me understand this or if I have the design wrong any pointers would be gratefully received.

Thanks
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Mark,
I have not looked at this real close, but a quick suggestion would be to use the query you already have, then using that, then join table3 to the matching join table in this query. do the same with the joins of table3 to table2 and table2 to table1. I believe you will then have what you are looking for.
HTH,
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
Glad you were able to recycle some of my SQL :)

Can you explain the UK postal code logic and why it's beneficial to split it up into components? I think if I understood it better I could offer a solution. What you're experiencing is called cartesian product and I believe it's happening because you have a many-to-many join somewhere.

hth,
Giacomo
 

Mark77

Board Regular
Joined
Aug 27, 2004
Messages
66
Thanks VicRauch.

I am not entirely sure on how to make that join, however I will try it and post back if I get stuck, I enjoy trying to sort these things out but sometimes just need that little bit of guidance. Thank you for your reply though it is appreciated.

Giacomo

The UK postcode system holds 1.7million full postcodes. The first part, the outward has 2900 combination. If you include the first numeric of the second half the permuations increases to 9500. The final two letters add the final totals.

What I was trying to achieve here was to avoid repeating data as each outward has various sectors which in turn have various walks. Each subsequent walk will then have a rating area for buildings insurance and one for contents.

Hope that brief explanation provides a little more depth for you, sorry I should have included this in the first post!

Regards

Mark
 

Forum statistics

Threads
1,141,679
Messages
5,707,787
Members
421,527
Latest member
Tamiwsw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top