Best option for grouping areas in a customer database?

[Garindan]

Hi all, just wondered if anyone would have an idea of the best way to implement this? I have a customer database, the relationships are below, and currently have done searching by town/city, street, purchase date, and also searching by area. But now the person i created it for wants to be able to search by 'groups of areas'. He has written how he would like to be able to search and they often include 3, 4, or 5 areas grouped together as one, and in only one or two instances he has included a small town aswell as a few areas.

Basically, it's been a while since i actually made the database (a year or so) and have not been using access much at all, so i'm having a bit of trouble working out how i'm gonna go about it and would like some idea's . I'm sure once i get back into the swing of it i'll be ok, but i feel like a newbie again at the moment

Many thanks for any help you can give.

 

[wazz]

the only ways i can think of are to:
- create separate queries with the specific areas (and/or towns) listed in the criteria
- create a multi-select list-box that the client can select the areas from; the selections will serve as the criteria. (i don't know how you could combine areas and towns that aren't related without using the first option).

i have never used a multi-select list box for criteria but there are threads here you can search.

p.s. i can't help wondering why the area and post code aren't in the cust table and why work numbers home numbers and emails aren't on the 'many' side of a 1:M relationship?

 

[Garindan]

Hi wazz, thanks for your reply.

The area and post codes are seperate as we don't have them for every customer so if they were part of tblCustomers there would be loads of gaps in the data. The phone numbers and comments are in a 1 to 1 relationship as there is only one of each per customer.

As to the area grouping, is there no way i can use a table or a query to link a group of area's to a group name? i.e area 1, area 2, area 3 - group 1; area 4, area 5, area 6 - group 2. And then just do searches by group name? Thats what i was thinking of and what the person the database is for would like, but i'm just not sure of the best way to do it.

Many thanks again.

 

[wazz]

i'm pretty sure that creating a table of groups, which you could do, would be a little more work than creating queries specifying certain areas. if you do it with a table you'd have to create tblGroup then link it to another table (one group, many areas) then create a join, etc, etc.

if you create a query, all you have to do is specify areas (1, 2, 3) in the criteria and you're done.

in both cases you need a way of letting the user know which areas are in the group. on a command button that opens the query (or report) you could simply put the area numbers.

i think i would go with the queries. (and you could (still) create a query that would allow the user to pick the areas on-the-fly when necessary.)

 

[Garindan]

No i've tried a few things but i'm stumped

It just seems really messy to have to have lots of different queries just to have different groups doesn't it? There must be a better way. I just want to have a list of groups (which are linked to areas) so u can just search by group, produce reports by group, etc. There will be something like 20 groups, so to have to have 20 queries, and 20 different types of each type of report is just going to be silly, and very messy, isn't it?

Help pleeeeaaase, , lol

 

[Garindan]

Ok i think i'm going to have to go with the multi-select list box option, at least he wont be fixed to only the grouped areas then and can just choose which ones he wants to bring up. So now i've just got to work out how to do it

Many thanks.

 

[neileg]

You can create a groups table and link your areas to the groups. This will enable you to run a query based on groups. It won't be so easy to include towns in the groups, though, because your areas are linked to the customer, not their geographic location.

If you have a look in the sample databases section of these forums you will find a number of search examples that will proably get you off to a flying start.

 

[wazz]

something i forgot to mention before: you should have a tblArea used as a lookup table for tblCustomerArea (unless every customer has their own area). the AreaID (or AreaName) is what can go into query criteria (maybe that will start to clear up the problem).

the other question is, can an area belong to more than one group? if so then building tables will, i think, be messier than queries.

for M:N tables you'd need:

tblArea (you need this anyway)
AreaID
AreaName

tblGroup
GroupID
GroupName (what do you call this? Group123, Group456, etc?)

tblGroupArea
GroupID (FK)
AreaID (FK)

if an area is only used in one group then a Group can have many Areas (1:N).

another idea off the top of my head would be to put another field into tblArea:

tblArea
AreaID
AreaName
AreaGroups (list the groups that this area belongs to, then lookup this data; this is a workaround of sorts which...)

 

[Garindan]

Thank you very much for both your replies

Sorry about the double post.

Wazz - i have started making a tblAreas, as you suggested, and have also created a tblTown/Cities aswell, so both will fuction as lookup tables aswell as allow me to use them in queries. I had wondered if i should have done this before.

I have created a table with AreaID and Area, and used an append query to add all the existing areas to it, so it is now ready, and have done the same for the tblTown/Cities.

However, i now have to update the roughly 3300 area's in the tblCustomerArea and change them to the appropriate AreaID so i can link the tables, and the same for about 5000 in the Town/City field of tblCustomers. I can't figure out how to do it, and obviously i don't want to get it wrong

 

[wazz]

I have created a table with AreaID and Area, and used an append query to add all the existing areas to it

skipping TownCity for the moment, first, please make a copy of your database. just highlight it in its folder then copy and paste. we'll both feel better.

i can't tell by what you said whether or not your tblArea is OK. do you have unique areas? or did you just copy every row from tblCustomerArea and put all of them into a new table? every row should be unique, ie. a different area. let me know...more after.

 

[Garindan]

Oh yeah, i know i appear to be totally useless lol but i do know a bit of what i'm doing, i certainly don't have a problem with copying and pasting a file , hav already done that.

No, i have done tblAreas properly, used a query to bring up existing areas then used SELECT DISTINCT, so thats all fine. Just didn't know how to do the next bit.

Thanks alot for you help by the way Wazz!

 

[wazz]

Oh yeah, i know i appear to be totally useless lol but i do know a bit of what i'm doing, i certainly don't have a problem with copying and pasting a file , hav already done that.

No, i have done tblAreas properly, used a query to bring up existing areas then used SELECT DISTINCT, so thats all fine. Just didn't know how to do the next bit.

heh. i thought from the start that you new what was going on, but i suddenly got worried for some reason...

k.

- if you haven't already, add an autonumber PK to tblArea.
- in tblCustomerArea add a third column - AreaID (data type: number).
- create a query with two tables: tblCustomerArea and tblArea.
- switch to SQL view and add:

UPDATE tblCustomerArea INNER JOIN tblArea ON tblCustomerArea.AreaID = tblArea.AreaID 
SET tblCustomerArea.AreaID = [tblArea].[AreaID]
WHERE (((tblCustomerArea.Area)=[tblArea].[Area]));

- check the results. you should be able to delete Area from tblCustomerArea and just leave AreaID.

 

[Garindan]

I'm sorry Wazz, i've been trying it but i just can't get it to work

I've started to work out how update queries work from what you gave me and i've tried a few things but it doesn't seem to work. With the code you gave it just says 'About to update the data in 0 rows'.

If i remove the criteria, it says about to update the data in 147 rows, which is the number of area's in the tblAreas, so obviously also the number of AreaID's, but then will say cant update 147 rows due to primary key violations. If i just clarify everything will that help?

tblAreas

AreaID - Primary Key, AutoNumber
Area - Text, names of the areas
It says 148 entries.

tblCustomerArea

CustomerNumber - Number, related to CustomerNumber PK in tblCustomers
Area - Text, names of the areas
AreaID - Number, blank obviously

3667 entries. When put in a query with Select Distinct, there are 148 entries, so it's correct, and i did do the tblAreas properly, they should all match.

I'm not sure whether the query said 'about to update the data in 147 rows' or 148 now

So, i feel bad now taking your time up, but any idea's why its not working?

 

[wazz]

it should say 'about to update 3667 entries...'

are you putting the tblCustomerArea in the update query still with Select Distinct? should not. both tables should be in there 'as is'. start with a select query with the two tables, no changes.

note the SET line of the SQL: SET tblCustomerArea.AreaID = [tblArea].[AreaID]; it's going to set the AreaID of tblCustomerArea to the AreaID of tblArea. (maybe you see that already). you could change the join in the query but it shouldn't matter: when you put the two tables in the query design, delete the existing join and then join the two Area fields. i think the main point is what i said above: don't filter (select distinct) the tables in the update query. let me know if that helps.

 

[Garindan]

Ok not the best way to spend a sunday afternoon but i got it to work, thanks alot Wazz.

No i had everything right the first time, and wasn't using select distinct. But in the query the two tables were joined on AreaID, as you said here:-

UPDATE tblCustomerArea INNER JOIN tblArea ON tblCustomerArea.AreaID = tblArea.AreaID

but then in the last post you said "when you put the two tables in the query design, delete the existing join and then join the two Area fields", so i did try that instead, i.e.

UPDATE tblCustomerArea INNER JOIN tblArea ON tblCustomerArea.Area = tblArea.Area

and it worked! Thanks mate

I have now done the town/city table in the same way and am in the process of updating any queries, forms and reports that need doing due to the changes, so will be all set to go shortly.

Now i need to just work out how to do a multi-select query . I'm not going to do the area groupings, i like the idea you gave of the multi-select box better as he wont be tied down to just the grouped areas but can instead choose which ones he wants. That'll be better wont it?

Only thing is i haven't got a clue where to start

Here's the updated relationships table by the way:-

 

[wazz]

hey great!

two things:
- should read AreaID (number data type) in tblCustomerArea (at least, it is good form to not change the names) (and don't use a lookup in the table)
- have to check but probably shouldn't use a slash in a name (change Town/City to TownCity)

(another possibility: could have 1 county to many cities/towns and link only the citytown to customer; county will be knowable through city. and, search for multiselect listboxes here, it has definitely been discussed, and there might be a sample db but i'm not sure.)

 

[Garindan]

Thanks alot for all your help wazz! I've changed the names of all the fields to be correct, and have linked counties to towns, so now the database has been given quite an extensive overhaul!

Only thing is, i'm so rusty on things, and i've found that i can edit the county names from forms and if i change the name of a county from one customer record it will obviously change the name of the county for all related towns! I want to be able to lock it so it can't be changed but i can't remember how

I have it as:-

tblCustomers
Town_CityID

tblTown_Cities
Town_CityID
Town_City
CountyID

tblCounties
CountyID
County

Thats ok isn't it?

Also, in forms, if i choose a town_city, the county doesn't update to reflect the chosen town, so it will enter incorrectly.

Edit: I have managed to get everything working properly now, and county updates with town/city. Only thing left is that i want to make it so the county name can't be changed. Can i lock the counties table? It has all 40 counties from england entered in it already so it doesn't need to be able to be changed.

 

[wazz]

- if towns are being selected from a combo box then you can put the county together with the town in the same combo and remove the county control.
or
- lock the county field-control on the form: properties/locked=yes; (poss: enabled=no).

 

[Garindan]

Thanks Wazz for all your help, i'm really gratefull

I'm having real trouble with the multi-select search now aswell but i'm going to start a new thread in the reports section as it's more appropriate and i think i've taken far too much of your time already sorry. And thanks again.