Store by region or country, search by postcode (1 Viewer)

[daniel.winson]

Hi everyone,

I am implementing a contacts database that will store details of organisations across the UK that provide Multiple Sclerosis support services.

Some organisations service the whole UK, others just a country, others just a region.

End users will search by postcode to find organisations that cover their area.

I attempted to keep the entry form as simple as possible but can't help thinking there is a better way to do it that I am not seeing... at the moment I've got a continuous form that enters region by region & tick boxes that I plan on linking to a separate 'country' table for organisations that service an entire country.

The unbound combobox above the location subform was going to have country names in it and then I'd auto populate the subform with regions depending on the country selected.

It is starting to get a bit messy and before I invest any more time in getting the bits and pieces working I thought I'd see if anyone can offer advise RE the overall design. Thanks for your help.

dan.

 

[David Eagar]

You might want to follow this thread, you seem to have similar requirements

http://www.access-programmers.co.uk/forums/showthread.php?t=172988

 

[Julian]

The problem is similar to a bill of materials, in that you have a hierarchical structure of data with postcode being a child and town being a parent.
The town itself will have a parent (the county), the county has a parent (region e.g. Scotland) the region has a parent (Country e.g. UK).

This is how bills of material are stored.

Your table would have the following fields
ID .........Region Type .........Region Name ........Parent Region ID
1 ..........(1)Country ...........UK .....................0
2 ..........(2)Region .............England ..............1
3 ..........(3)County ............Berkshire .............2
4 ..........(4)Town ...............Slough ...............3
5 ..........(5)Postal Area .......SL1 ....................4


In this case you have 5 levels.

If a organisation deals with the UK then it will have a service region ID of 1.

If an organisation only deals with Bershire, then it will have a service region ID of 3.

To search from an area e.g. SL1, you need to find each parent of each region, and then perform a UNION query on each level for organisations that cover each.

Issues:

Ensure that each parents level is higher up the hierarchy than the child level. (put a check in that tests the level of the parent on entry)

Think very carefully about your heirarchical levels before implementation.

 

[daniel.winson]

Thanks for the help, honestly I think I might just take the easy way out and make the database for Scotland only, the rest of the UK was just future proofing it anyway.