My Database... Isn't very good!

[DavidMcArthur]

Hi Guys,



I wanted to share with you a project I am working on, its getting to be a bit because i cant work out what next steps i need.

Its a client/quote/general database for all of my clients and project.

Currently what i want and almost have is;

Client Details - Individuals names which we do work for.

Company Details - The main companies which we work for.

Site Details - The construction sites we work on.

So one company from the Company Details section can have many Construction sites on the go at any one time. They can also have lots of people working for them (which i call clients) who also work on their construction sites.

Still following?

I want to be able to successfully link all of these three elements together. Ultimately i want to be able to create quotes and invoices from this database too.

I am by no means asking for anyone to complete my database - I just want some of your expert opinion on how best to move forward! as its confusing me!! haha!


https://www.dropbox.com/s/a775e4yrqy...QDB.accdb?dl=0

The drop box link as its not letting me upload.

[ranman256]

A tProject table
ProjID,CoID,ProjDate,ProjName,BidAmt

tSite tbl
SiteID, ProjID, SiteName,SiteAddress, notes

tProjectWork tbl
SiteID, EmpID,WorkDate,Hrs,RateCode,HrType, JobType

[DavidMcArthur]

A tProject table
ProjID,CoID,ProjDate,ProjName,BidAmt

tSite tbl
SiteID, ProjID, SiteName,SiteAddress, notes

tProjectWork tbl
SiteID, EmpID,WorkDate,Hrs,RateCode,HrType, JobType

Hello! The example you gave is maybe for my employees working on sites? Its more about my clients and their construction sites. I see the general structure though. Is the ProjID, SiteID & EmpID all Auto Numbers? There would be no prices going onto this part.

[ranman256]

yes, the ID fields would be AUTONUMs.

[ssanfu]

I'm not certain of your needs, and I think you are a little unsure also.

I stole the following from a post by orange - it is better than anything I could write:
---
"I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
<snip>

<snip>
I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
---


I understand the relationship between Company and Sites:
1 company can have many sites and 1 site belongs to 1 company. This is a 1 to many relationship.

I am not sure where Clients come in. And I do not see a table for projects.


Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.

Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated field in tables.
Do not begin object names with a number.

[DavidMcArthur]

Do not use look up FIELDS, multi-value fields or calculated field in tables.

Thanks for this. Very interesting to read. You're right I am a bit unsure but also I know exactly what I want/need, if that makes sense?

The quote above really stood out for me too. When doing Relational Databases (I done 2 Years on it a while ago) I was always doing LookUps to other tables.

For example

Company_Details
Company Name PK
Company Address
Company Address2

Client_Details
Client Name PK
Company Name FK
Client Mobile No.
Client..
Client Etc.

[CJ_London]

I was always doing LookUps to other tables.

ssanfu is referring to

Do not use look up FIELDS, multi-value fields or calculated field in tables.

they are OK for forms and reports. If you are using them in tables, remove them.

And regarding your tables, using a name as a primary key is a really bad idea. it should be

Company_Details
CompanyPK
CompanyName
CompanyAddress
...

Client_Details
ClientPK
ClientName
CompanyFK
ClientMobileNo
...

[ssanfu]

For clarity.... Look up TABLES are different than look up FIELDS (in table).

Look up TABLES - Good
Look up FIELDS (in table) - BAD

And regarding your tables, using a name as a primary key is a really bad idea. it should be

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


I always use an autonumber field as the PK field in my tables. But I never display the autonumber field on a form or report.

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

[DavidMcArthur]

Thanks, great advice.

Okay so ive redone my database and only used the autonumber as the FK but how do i get the information from my FK table into my main table? Is that through the use of queries?

[CJ_London]

don't understand the context of your question - what is your main table? you've only mentioned company, client and site. How do you do it now? You seem to be implying FK is an auto number. PK's are autonumbers and FK's are long