relationship trouble!!!

[pollypocket]

please help this is driving me mad!!!

i am trying to set up a database dealing with customer complaints. I currently have tables as follows

tblestablishment - name of place etc
tblcustomers - firstname, last name, address etc
tblcomplaint - there are several different complaints such as service, cleanliness

the problem im having is i need to know different information for each complaint ie service (where, staff invoved etc).

If in tbl complaint i have a lookup box listing all complaints how do i set up different tables with all the required information relating to that particual complaint and relate them???

pls help!!!

 

[Mile-O]

Detail your tables in depth - what other entities will you be needing to store? i.e. staff, costs, etc

 

[jon98548]

What about something like this. As I understand the structure, each member of the staff would have a unique record so their individual information would be in one table. The same goes for customers. Since you want predefined complaint categories, give them a table of their own. Then you want to log complaints. A complaint would be a record in the log and gets a unique ID. Customers, Staff and Complaint Categories are all related one to many to the Log. Each complaint would have its own details. That's how I would start. Mile?

 

[Mile-O]

Sent to me by pollypocket
We have 20 shops in the country each of which are controlled by an area manager. my first tbl will list all area manager details, name etc. the second tbl will be a list of shops. i have created a 1-many relationship between the area manager tbl and the shop tbl so each area mgr has a list of shops they are responsible for. i then need to create a tbl with general complaint details ( date, where, who took complaint etc) and then i need different tables set up to relate to different complaints and lastly a customer tbl so in the end the customer relates to the complaint the complaint relates to the shop etc.......

Is wasn't long in leaving work for the day so I thought it best to post the message on the forum as there will be better suggestions through the (British) night, I'll bet.

 

[Fornatian]

This is the set up I'd suggest...

 

[jon98548]

I agree. But just to nit pick, wouldn't it be good practice to throw in an ID field in the Area Mgr table?

 

[pollypocket]

here we go Jon

Hi Jon, Thankx for all your help so far,

Please find attached what i have structured so far, i also need to add a resoloutions table but im not sure how to establish a correct relationship and with what table.Im still fairly baffled by 1-1 1 - many etc and im not sure of the user friendliness of what ive structured.once again thankx for taking the time

 

[Mile-O]

Just thought I'd say I can't view your example as it is in a version greater than '97.

If you can, please convert it to 97.

Tools -> Convert Database


I'd go with Fornation's design although I wouldn't have a field called Date.

Maybe an extra table of complaint categories, too.

 

[pollypocket]

convereted i think!

 

[jon98548]

Polly, here is what I think. You other guys jump in if I'm off. You can go to any level of detail you like. If MetodofComplaint still applies, you probably want a table for that, i.e. tblMethods. Most of the time, I use the Lookup Wizard in the design view of the table to make my links. In tables where you have have a unique number, such as employees, you can use their employee number or where you have in-house generated codes like a complaint code. In most other situations, let the ID be an Autonumber field. Say you make the table, tblMethods. Let that autonumber the ID field. Hope I haven't made the water muddier for you. Mile? Fornation?

 

[jon98548]

Here's one for you, Mile. Since my version is GREATER!

 

[pollypocket]

JON,

In tblshops, the am field im asked to enter paramter value it then lists am surnames how do i iget both first name and surname?

 

[jon98548]

Oops! Made a change and didn't carry through. Open tblShops in design view. Go to the AreaManagerID field and go through the lookup wizard in the data type.

 

[Fornatian]

I'd go with Fornation's design although I wouldn't have a field called Date.

Mile, bad practice I know but, come on dude, give a guy a break

 

[Pat Hartman]

No breaks on stuff that can cause problems

Jet Reserved Words

Access Reserved Words

 

[jon98548]

I updated this a bit. Saw a double link I got rid of.

Also, I got to thinking about the customers. If there is only going to be one customer per complaint, which i assume would be the norm, I don't think you need them as a separate table. I would incorporate their data directly into the complaint table. If this was a database where you dealt with customers on a regular basis, i.e., a bakery that orders your pies every day, then you would want a separate table.

Notice in your query to search for a date range, you Between [Parameter] And [Parameter].

 

[pollypocket]

Hi Jon,

the information which is required on this weekley report is bits from different tables eg customer first name and srname from tbl customers shopid from tbl shops area manager id from tbl area managers...etc....i have placed the criteria under date of incident but still no records show....baffled!!!

 

[jon98548]

Here are a couple more updates. Notice the parameter statement in the query. Everything is working fine in this one.

One other thing I meant to mention. If you leave a null value in a field that is linked to a table, it will not come through to your query. I always make sure I have some kind of default value in those fields to ensure all information comes through. I don't know the details of why and when - I just know how I work around it.

 

[pollypocket]

great stuff, thank you seems to working for me ok now...one minor question! how do i get thedate range i.e 02/09/03 - 09-09/03 to show on the report automatically also is there a way to get customer first name and customer surname to show with a only a small space inbetween instead of a huge gap between the two fields

 

[jon98548]

Check out the report in that last version. In the text box for the customer name and the other name fields for that matter are have their data fields set to:

=[tblCustomer.FirstName] & " " & [tblCustomer.SurName]

Of course, for the manager's name put in tblManager and so on.

Some one correct me if I'm wrong, but I think if you use an input box when you report opens those values can be used where ever you like. Have to check into that one.