How to Implement??

[radick201]

First off I am very new to Access and just starting to learn how powerful and useful it is for my scope.

I am trying to put a construction project database together which will include Projects, Venues, Subcontractors, and Trade/Specialties. When creating a new project I would like to choose from the list of Venues to use but if not already created in the venues table, I would be prompted to create it first. Also to be able to select multiple trades that would be applicable to that project. Subcontractors would also be able to choose what trades and specialties they can do and have that information tied to the project so when I create a report in the future, I can just select the trades I want and then the subcontractors would be shown with their company information.



Any help would be much appreciated. I am not looking for an exact coding or step by step but any tutorials or sites you might think would be relevant to helping me learn the procedures would be ideal.

Thank you

[orange]

One approach, recommended, is to start with a data model. Here are some links

Overall design http://www.rogersaccesslibrary.com/forum/topic238.html

Entity relationship diagramming
http://www.rogersaccesslibrary.com/T...lationship.zip

http://www.rogersaccesslibrary.com/T...20Nutshell.doc

Also, I'd recommend you take each thing(Entity/Table) and write a 2 or 3 line description of exactly what this represents. You'd be surprised how much you're sure about somethings until you start getting into the details,but writing it down helps.

Make some sample data, both good and bad data, and test your data against the model. Reconcile every discrepancy and adjust the model or data accordingly. Getting the underlying structure correct is the most critical step.

Don't be too quick to get into the details of Access.

Good luck.

[radick201]

Thank you for the quick reply.
I went through a majority of it already and I know I am having some trouble with the actual structure of the Entities.
I now have the following:

tblProject: ID, Client, Venue, Due Received, Due Date, Labor Req., Trades
tblVenue: Street Address, City, State, Zip, Labor Req.,
tblSubContractor: Company Name, Street Address, City, State, Zip , Labor Shop, Contact Name, Phone#, Cell#, Email
tblLabor:type
tblTrade:type

The problem I am having or idea of grasping is the multiple to multiple relationship. In my case, a Project can have Multiple Trades, Each Trade can have multiple SubContractors but every SubContractor has the possibility of having multiple Trades to perform, eg. an electrical contractor can do both low voltage work and can also do fire alarm work. Do I need another table to express the Trade/SubContractor relationship but I think that would be empty since all the specific trades are within the trade table.

This same problem also applies to Labor and SubContractor. Within Labor there are 3 types: Union, Non-Union, Open (which can be both). A venue can have multiple Labor Requirements and contractors can have multiple labor requirements as well.

Any help would be appreciated.

Thank you

[dblife]

looks as though you are discovering how quickly table structure can grow.
you do not always have to have a table for everything if you want to keep the numbers down but having tables makes life much easier in the future.
also, there comes a point where you can try to cover everything and end up tripping yourself up.
before you write the table into the structure, is it actually needed in the scope of your day to day job. does it matter if someone is union or non-union?
if you want, you can write the tables, use access to create the relationships between the tables, take a screencap and post it in this thread - it is always a good idea to have the entity relationship diagram for your database at hand when designing forms, queries etc anyway.