Relation Issue -- Really Need Your Help!!

[BrentG]

Hey guys,

Im working on a project for a landscaping company that does Installation and Maintenance Services that wants to be able to build contracts, bid and log its customers into a simple database and I've run into an issue I cant resolve.

I've normalized data so far as follows:

tblCustomers --> tblProperties (as customers may have more than one property) --> tblContract (think of as receipt, as properties may have more than one business order) --> attached lookup (called tblContractType) re business type (property maintenance OR Installation)..

My problem is that for maintenance and installation there is a completely seperate price breakdown, its not as simple as dealing with inventory items. We are building the bidding into the database and reports of bidding will serve as contracts.

Should I put all the data for subservices for both installation and maintenance (ex. grass cutting AND grass instalation, pruning AND tree installation, etc) into one sheet titled "tblContractServiceItem" with a Foreign Key which ties to a specific Contract Number and then use queries to single out specific data, forms to build new contracts, and reports to produce actual contracts (This will be somewhat complex in its own right)?

... or have I gone offcourse somwhere?

And one more Q: If I wanted to add a seperate table for alternate addresses for customers, would I simply apply an autonumber for each entry (key) and then relate that to a AltAddress field in the Customer Table... how would that work?


Thanks Guys, I hope this was clear enough.

Cheers,

BG

 

[dsigner]

Just some quick thoughts.
Contract type would be better as a field in contract. It can be a look up field and use a data type table if you wish but this may not be necessary if there really are only two types.
What seems to be really missing is a line item table for the contracts. This is lniked on contract ID. Now you can have a line item type which enables you to calculate grass cutting form dimensions/area of land and frequency etc.

This will also produce an itemised invoice at the end of the period.

Hope this helps.
If you really want detailed help then I think the guys here would need to see the table structure as an attachment.

 

[BrentG]

A few New ideas... Need more help!!!

Hey disgner,

I've attached the relationship layout as you reccomended, though some things have changed since my last post.

1. I've had to split the company, contacts, and locations into three different tables as our contact person X may look after locations A and B, where are owned my companies Y and Z who are themselves based out of locations C and D, which we dont maintain... and our contact works for company Z1 based out of location E!!! (most times its not that complicated, but the system needs to be able to incorporate that if need be)

Issues Im facing with this:

Using a query and form to enter all of this data in within one form (see form attachment) BUT I dont know how to make the program recognise if Im inserting a new property for the same company. It adds a new company even if I type the same name it. And futhermore, with this form its only the query that yields any decent data (query same as the form gathering infor from respective tables), the tables themselves have no reference to the address or name of the contact!!

Im ass-backwards with this thing dsigner, please help me out!!

BG

 

[Banana]

First of all,

1) Your keys should all have the same name. Use ContactID for all fields that reference the key. You have your "BillingCompany" as the many side to BusinessNumber, which will certainly cause you and other people to pull hair out of your heads.

2) You may have a circular relationship between Business, Contact, and Property. I can't see all the fields in table so I'm not 100% sure, but if it goes in a loop, it may cause a problem.

Understand that when you create a new record, you will have to fill in all related fields, especially if you have RI enforced. If the related fields in turn have their own related fields, they will need to be filled in turn. However if the required field refers back to the original table, you have a problem because you can't save the record, and without it saved, you can't update the last field.

That said, you really will want to compare your inventory database to other samples on inventory samples available here or at MSDN or even with Northwind that came included with your copy of Access. Study it and understand how it all comes together.

 

[BrentG]

Thanks man,

I know that last setup was right messed and chaotic... I'm reforming it as I type this... I'll post futher questions specifcally in a new thread.

Thanks again.