Complicated Select Query

[Merdok]

Hi guys this is my first post on here so I'll start by saying hello to you all and I hope I become useful to people instead of just being a drain on everyone elses knowledge... you never know!

Anyway, to the point:

Basically I am making a rather large access application for a damp proof course supplier with many clients in the building trade.

Each client is split into regions and each reigon has a number of buyers who change from time to time.

I need to create a drop down box for each region with a list of all the current buyers in it, the list needs to be in a record in (or linked to) the clients table (or it could be a query if this is easier).

It has to be editable (Add/edit/remove buyers) each buyer must be selectable in a drop down box so it will work with a later phase of my application.

If anyone can help I will be very greatful.

Regards,

Alex.

 

[Mark Wild]

Merdok,

Not sure what your exact question is but it sounds like you need a form with a combo box. Have a not in list command (plenty of examples on here) on the box to allow you to add buyers etc. To delete a record, would advise having a button that picked up the record in the combo box and used this to complete a delete statement.

I'm assuming that you hold the info in a number of tables that are linked (region, buyer etc). To only show buyers for a region, set the criteria for the buyers combo box to only show records that are related to the info in the reion cmb.

Have a go at this and if you have any probs, have a good look through the forum using search - you can often resolve your porbs this way. Otherwise, try to post questions that highlight your exact problem.

Enjoy the forum. If you learn as much as I have it will be well spent time!

 

[AncientOne]

Well, it won't take long to drain my knowledge!

What you are talking about is a basic process in Access. Your buyers and their regions are stored in a table. Your "drop down" boxes can be either combo boxes or list boxes which draw their data from those tables or a query based on the tables. As long as you maintain the tables(via an editable form), current records will always be available. You only need one combo which can be filtered to only show buyers from a given region or from any combination of regions. The relationship between buyers and clients is established by having a "foreign" key field in your client table. (assuming that one buyer has many clients). You need to check out one-to-many relationships to understand this)

The Northwind Database will give you an insight into combo boxes or you can use the combo box wizard to test out your ideas. One-to-many relationships are also well covered in Northwind.

 

[Merdok]

Well most of that made sense to me,

It sounds like that its the query itself that I'm having a problem with then.

Currently I have a clients table which looks like this

Client ID: Autonumber (primary key)
Client Name: look up to 'client name' table
Client Region: Not individual
Buyers list: currently not doing anything as I cant figure out how to display only the buyers from the appropriate client/region combination.

and other things which I dont think are relevant. The chances are I just cant get my head round the query.

 

[Mark Wild]

Hi Merdok,

The first thing that I would do, and it will help you later on, is to normalise your tables as per AncientOne's advice.

You will probably end up with a client table (naming conventions such as tblClient etc will benefit you later on)

Client Id
client Name
RegionID

Then a Region table with

RegionID (autonumber)
RegionText
BuyerID

and finally Buyers

BuyerId (autonumber)
BuyerFirstName
BuyerSurname
All other details broken down to the lowest form

Using one-many relationships, set the joins for the tables.

This structure allows (forces?) you to make every buyer "belong" to a region, which then "belongs" to a client.

HTH

 

[AncientOne]

How many buyers to 1 region?
One customer is in one region?
One buyer has many customers?

 

[Mark Wild]

Merdok, use my stuff as an example, but you need to apply the questions that AncientOne asks in order to build the structure.

In my example, I have assumed that each client will only have one region, and that a region can be for many clients.

Also, that a buyer will only have one region, but a region can have many buyers, and this may not be the case.

 

[Merdok]

AAH this is starting to make sense now... right I'll give that a go and then I'll be back to either thank you or whine some more.

How many buyers to 1 region?
One customer is in one region?
One buyer has many customers?

There could be as many as 10

The customer would be Joe Blogs Builders for example and the region would be north west, one customer could have many different regions, also both Joe Bloggs and John Smith could have offices in the same region so there could be a JB north west and a JS North west.

The buyer is litterally only there for a mailmerge bit later on, the buyer is just the point of contact.

The heirarchy would be something like this:
___________________________________________________

COMPANY NAME: eg, Joe Bloggs
Company Region: eg, London
Region Buyer (as point of contact)
Building Sites
Site Agent (as secondary point of contact)

However the building sites will be added in another table and will be entered manually and the site agent does not need to be inlcuded anywhere in the database.
____________________________________________________

 

[Merdok]

I have attached a snapshot of my current relationship/table layout, which may help.

 

[Mark Wild]

Hi Merdok,

Jsut a couple of point that come to mind from this.

1) Aviod spaces in field names , ie Client Name should be ClientName. This also applies to tables ie tblClientName

2) I dont know what you're storing in clients old, but you might be better keeping all data in clients and having an extra field for old. This removes issues such as moving records from one table to another, deleting records from the original table, worrying about the relationships etc.

3) You should aviod joing on fields such as client name, and use a unique field such as autonumber. What would happen if you had 2 Mike Smiths???

4) A table that has only one field in it will probably have a one to one relationship with the other table. In most instances, this means it could just be in the parent table. For example, in the client table, you have the client id and client name. Why will the client name in the clientName tablediffer from the one in the cleint table?

Client details and Buyer looks correct. With out further info about what is in Product list and current terms I can't comment, but not sure about them.

ClientDetails to Products should prob be linked via code as code is the PK, and hence unique. Have code in the clientsDetails table and remove the Product Description.

 

[AncientOne]

I regret to say that your tables/relationships are a bit haywire. You don't need the Client Name table at all. You don't need the product list table. You probably don't need the Terms table. If you do,it should be linked to clients via an Autonumber field. What are Buyer List and Product list doing in the client table? Why is product description joined to product list? As referential integrity is not stipulated, it would be possible to enter a non-existent product in your Client details table , anyway!

On the question of having companies spread out over different regions, you are probably better off treating them as separate entities. You will be dealing with different contacts at different addresses with different buyers and site agents.Transactions may well be managed at local level, so there is no point in lumping all branches together under one client ID.

With this scenario, your BuyerID can go into the client table as a foreign key. The client ID mustn't go into the Buyers table, otherwise you'll violate the no duplicates rule for that table because one buyer has many clients.

 

[Merdok]

I regret to say that your tables/relationships are a bit haywire. You don't need the Client Name table at all. You don't need the product list table. You probably don't need the Terms table. If you do,it should be linked to clients via an Autonumber field. What are Buyer List and Product list doing in the client table? Why is product description joined to product list? As referential integrity is not stipulated, it would be possible to enter a non-existent product in your Client details table , anyway!


[alex] The Problem is, I'm quite new to this and I thought this was the best way to do it. I'd show you the full database but its over 10 meg.[/alex]

On the question of having companies spread out over different regions, you are probably better off treating them as separate entities. You will be dealing with different contacts at different addresses with different buyers and site agents.Transactions may well be managed at local level, so there is no point in lumping all branches together under one client ID.

[alex]The problem arises with the product list, the product list is a list of companies who have a seperate set of prices and some specialised products for them only, this is company specific and not region specific. Also for reasons of organisation, the companies have to be grouped by company and then region. So I cant think of another way around it.[/alex]

With this scenario, your BuyerID can go into the client table as a foreign key. The client ID mustn't go into the Buyers table, otherwise you'll violate the no duplicates rule for that table because one buyer has many clients.

 

[Merdok]

Right... Sorted... just needed a good nights sleep and a coffee and suddenly all your help made sense.

Thanks Nescafé.

Cheers for your help guys.