Relationship Question 1

[nedstar1]

Hi friends,

I've buit a database to help my bosses track orders through the purchase and sales process. I built a form that tracks everything by job number, based on tblJobs. tblJobs in turn has SONumber and PONumber fields. Both tblSO and tblPO are tied to JobID.tblJobs in one to one relationships, as there will only be one PO and SO per order. I have linked subforms on the main form for SO and PO info. These are tied using master child fields to the relevant fields on the main form - tblPO subform has PONum as child field to tblJobs.PONumber, and tblSO subform has SONum as child field to master field tblJobs.SONumber.

Everything works great - except for the eventuality that we may receive and enter data for a PO before it is issued and we have the PO number. In this instance, any info entered on a given job shows up for all orders without a PO.

I know I must need to edit my realtionships to get this resolved. Unfortunately, I'm not sure if I should reference the PK from tblJobs or something else.

Do I need to change my form design or my relationships to correct this? Any tips?

TIA

Regards,
Nedstar1

 

[LittleSmudge]

If it is a One-to-one relationship then WHY oh WHY is not all in the one table ?

There is no need at all to have separate tables tblSO and tblPO.

Stick all the field in the one table and your forms will all be neater and easier to lay out because you won't need subforms and you main problem as you state it above will go away too.



'ope-that-'elps,.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[nedstar1]

Sorry for not replying sooner. I have over 400 relevant fields, and I expect to add quite a few more as the bosses review the available data and want more. I hit the cap during my first iteration, and then I decided to cut out the PO and SO info into seperate tables to alleviate this and allow room to grow as necessary.

Hope that expanation suffices.

I have the info appearing correctly now - my issue was an erroneous master/child relationship in the form/subfrom design. I am still finding it possible to begin to create a second entry per job, but it will now error on trying to move tot he next record until I clear the fields.

Something is still odd here, but I'll figure it out.

I appreciate the feedback.

Thanks
Regards,
Nedstar1

 

[PHV]

More than 400 differents fields for each job number ?
I can't believe it...
This may be of interest for you:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nedstar1]

Hi PHV,

I read that before I even started. It's very very illuminating, but nothing like getting your hands dirty and trying it.

To clarify, I have a lot of info for each job that needed to be tracked - component information, materials, colors, imprints, imprint types, PMS colors, etc - this adds up to nearly 150 fields all by itself. Additionally, I needed to have info on purchase and sales orders, and I needed to limit access to this pricing info based on user. I'm working on pulling out the contact info for the POs and SOs into a contacts table, building lookup tables for other things, but for now, yeah, it's freaking bloated.

But it works. For a newbie effort, I'm happy as can be.

Regards,
Nedstar1

 

[willir]

Hi NedStar1

This is quite a project you have, dating back to May 2004. Quite the task.

All right, your database issue is complex, you have done your home work, and you seem to feel that the best way to accommodate the properties of each pen is to have different fields types, and perhaps different tables for different pen types.

eventuality that we may receive and enter data for a PO before it is issued and we have the PO number

You can use a primary key that is not the PONumber. this will allow you to track the info with providing the PO Number.

tblPurchaseOrder
POID - primary key
PONumber - string / numeric, index unique, nulls allowed
+ other PO info

You can link to your other tables using PONumber or POID, although PONumber is now more of a descriptive field.

...Moving on
I think I may have a partial solution for your deisgn dilema.

Here is a link to a database that I use to track computer hardware among other things.

http://www.tek-tips.com/viewthread.cfm?SQID=860190&SPID=700&page=1

I now realize that it is actually fairly similar to your issue.

With computer devices, there are certain traits that are essential to all device - Manufacturer, Model, device name, etc,

...And the wrench in the works, various categories of devices have their unique properties. Workstations: CPU type, OS, drive(s), RAM, video, etc. Printers: printer type, printer speed, colour, RAM. Switches, Routers, Servers, Monitors, etc.

...Plus some devices have a similar property, and others do not. For example IP address for routers, switches, servers, printeyrs ... but not for monitors, peripherals.

What is really weird about of this design is that there is a one-to-one between the DeviceMaster, and the different device types.

Would this design fit your needs?
Have a PenMaster table. And then have differnt tables for unique attributes akin to having different tables for each pen. ...with a one-to-one relationship between the master table and the associated tables.

Richard

 

[knuckles98]

I have 3 table created: Contacts, Movie Collection, Game Collection. I am trying to create a one-to-many relationship between the contacts table and the movie collection table; also for the contacts table and the game collection table. What I'm trying to do is track who is borrowing what and when it is borrowed. When I make the relationship for contacts and one of the other tables, everything works fine, I can see who has what in form view or datasheet view. When I try to create another relationship with contacts and the remaining table, i get the message to the effect that it is to complex. Can you please give me some tips. Maybe I went about making the table wrong...Thanx!!

knuckles98

 

[willir]

knuckles98
You may want to post this as a separate thread...

 

[nedstar1]

Hi Richard,

I looked over your design for the tracking database - it does indeed accomplish many of the tasks I needed. I'm going to bookmark it and review it when I have some more time to devote - populating tables now for the big debut.

Both you and PHV have been of invaluable assistance. Hope to speak with you both here soon.

Regards,
Nedstar1