Design/relationship question

[Steven811]

Hi

I have a hierachical structure of:
One customer to many sites
One site to many contacts
One contact to many jobs.

I also need to show that a contact may/or may not report to a manager at a regional office and that the RO may have several managers. I have created a structure and enclose an example for you to view.

Being so close to it I am finding it hard to be objective and would really appreciate an opinion before I bring the data back in and start recreating forms etc.

Thanks

Steven811

 

[KenHigg]

Did not get the example.

kh

 

[Steven811]

Must have been me.

Here it is.

 

[ecniv]

Ok... I am wondering if you need RO and manager tables. Surely these are covered in Site tables and Contact tables and just need a flag/pointer to themselves?

So you'd have then:
Customer
- site
-- contact (manager)
-- contact
--- job
---- project
---- supplier
---- supplier
--- job
---- project
---- supplier
---- supplier
- site
-- contact (manager)
-- contact
etc..

At least I think thats what you have atm.
Seems a sturdy layout.
Also seems to be a lot of duplication/fields.

Possibly you'd want an address table then you can link to ids in that address table from customer, site, supplier etc? You've probably thought of that.


Vince

 

[Steven811]

Duplicate tables

Hi Vince

Thanks for taking a look.

I was conscious of the duplicate fields in the tables and had decided originally to include designation fields of RO or manager in the original contact and site tables. I couldn't work out how to cope with more than one manager at each RO and more than one contact at each site, that's how I came up with new tables.

Regards

Steven811

 

[ecniv]

Ah ic...
Lemm have a think... right.

Customers

Sites

Contacts
IsManager - Y/N (default False)

This links the look ups of site and manager/contact together...
ManagersAtSite
MASID - auto pk
CustomerID - number (not sure if you need this but you may)
ContactID - number
SiteID - number

Its quite tricky to get the setup right so you don't duplicate anything and that you have the links in place especially for the queries later on

Also, I would have thought you would have projects which then lead to jobs and suppliers, not jobs that lead to projects.... but thats just me.

Good luck finalising it
The above is only an option, perhaps it will give you an idea of how to do things.


Vince

 

[Steven811]

This is what I started with

Hi Vince

This was my original structure which works okay, almost. I need to be able to reflect the hierachical nature of the relationship between the contacts. For example a site contact will report to a manager, although their info is all held in the contact table.

This is most problematic when I use the forms I have created to enter relevant info. Enclosed is a screen shot.

I didn't want to add a filed in another table that duplicated data that is held elsewhere.

Any suggestions would be appreciated.

Thanks

Steven811

 

[ecniv]

Hi,

Nice screen
Hope that works fine


Back to the Manager problem...
Are the managers detail held in the contacts table (I saw the first time that they were nearly the same layout)..?

Almost like my last post , but you could have a manager to contact table which holds:
ManagerID - number (contact ID)
ContactID - number (the person who reports to the manager)

The other reason for putting managers into the contacts table is that the layouts are similar so you only need the one form then, instead of two. Also if you flag the manager with a yes/no field, you can always allow users to have a list of managers easily.

As you are the developer you'll have to weigh up which idea is best and go for it. I learnt fast but I had to try out the ideas, which is not always possible in work situations as they people always want something working by this friday, but if you can, try out as many different ways and layouts, as then the one you finally choose you can state your reasons why you chose to make it that way.


Vince

 

[Steven811]

Contacts table

Hi Vince

Thanks for your input.

All of the contacts are in one table, managers have a flag field.

I'm resigned to playing around with it until I can get a workable solution, the deadline will have to wait.

Regards

Steven811.

 

[ecniv]

Hi,

I had a quick go, rough and ready at the tables, mostly concerning the offices and managers/people.

I've attached a db in a zip file for you to look at. See what you think, based on your design.


You find it useful, you may not. Its just another option and with Dbs there are some many options you could take Good luck doing it I'm sure it'll work well.


Vince

 

[Steven811]

That's great

Hi Vince

Thanks for that, I will look at it later this evening.

I have got a structure working, but I'm struggling with a requery of a nested subform control (cbo).

How are you with a requery?

Regards

Steven811

 

[KenHigg]

forms!frmMain!subfrmMain.form!myControl.requery

???
ken

 

[Steven811]

Reply that passes my by

Hi Ken

Thanks for the reply.

Your reply suggests only 2 forms and the control I have is in the 3rd form.

Sorry if I've missed the plot.

Happens a lot.

Regards

Steven

 

[KenHigg]

forms!frmMain!subfrmMain1.form!subfrmMain2.form!myControl.requery

I think this work - never used it!!

kh

 

[Steven811]

Looking for a field

Hi Ken

I have used the following:

Private Sub cboLocation_AfterUpdate()
Form!forCustomerDetails2!forSiteName.Form!forContactsJobTracking.Form!cboLocation.Requery
End Sub

Access says that it can't find a field with the name of my 1st form.

It also changes my lower case forSiteName to ForSiteName which makes me think that it's looking for something else.

Any ideas?

Regards

Steven

 

[KenHigg]

Is it possoble you just missed the 's' in the first 'forms!forcus...'

Form!forCustomerDetails2!forSiteName.Form!forContactsJobTracking.Form!cboLocation.Requery

Should be:

Forms!forCustomerDetails2!forSiteName.Form!forContactsJobTracking.Form!cboLocation.Requery

?????
==================

Else:

Forms!forCustomerDetails2!forSiteName.Form!forContactsJobTracking.Form!cboLocation.Requery

forCustomerDetails2 = Main form

forSiteName = Name of first subform as shown in the subfrom control properties, not necessarily the name of the form as you have it saved in the database window.

forContactsJobTracking = Name of the second form name. (Ditto comments)

cboLocation = name of combo box

???
kh

 

[ecniv]

Depends where you are when you want to requery it and whether it is in a form or a subform.

Forms("<form name>").controls("<combo list name>").requery

or

<FormName>!cboLocation.Requery

if its a subform and you are in the code of the form, then <subformname>.requery

If its a subform of a subform and you are in the main form
<subformname1>.<subformname2>.requery

Let us know what happens.


Vince

 

[Steven811]

No more error messages

Hi

I've resolved some name issues, subform properties displaying a modified name to the form name in the db menu.

The code in there now is:

Private Sub cboLocation_AfterUpdate()
Forms!forCustomerDetails2!forSiteName!forContactsJobTracking!cboLocation.Requery
End Sub

However it does not requery until I close and re open the form. Although I now don't get the error messages.

I have changed the event to OnClick to no avail too.

I'm going to carry on exploring the options.

Any suggestions would be appreciated.

Steven811

 

[ecniv]

Use the debug breakpoint and check its running the code.

You may need square brackets in the forms listing...

Forms![forCustomerDetails2]![forSiteName!forContactsJ obTracking]!cboLocation.Requery


No idea.. poss the repaint method. But make sure its actually running the code first. Sometimes the code is there the event says its running, but with a breakpoint it doesn't run and needs to be redone and saved...


Vince

 

[Steven811]

Thanks

Hi Vince

I've had a look through the archive and can't find anything so I will post a new thread and see what happens.

Thanks for your help.

Steven811