Database Design and Layout

[rhys.downard]

Hi There.

I am struggling to get my database to work and I am starting to think that it is the layout that may be causing half the problems:

I am building a database to account for billable hours to a client anf then calculate an invoiceable total: My Variables are:
1. Billable Hours
2. Billable After Hours (Rate Multiplier used as a percentage of 'Billable hours X Rate)
3. Type of support (Adhoc, Contract)
4. Specific Support Rate type (Adhoc and Server rate: Adhoc and PC)
5. Contractual clients and the associated rates
6. Location of Support (Onsite, Remote, Telephonics)

There are other markers but they are just indicators for report purposes. The structure I am using is:

1. Job Card Table
- Fields: As above

2. Tables:
- Rates
- Clients
- Support type
- specific support rate type
- Location of support

For these tables I have edited the individual field to assign values in a combo box or set default values (Which I cant seem to access in a form).

I am also struggling assigning Primary keys to various tables.

I am thinking that perhaps I need to combine everthing into one table and just assign values to the Fields( Not for clients table).

I need to be able to run calculations on the tables and fields.

So then I also need to know how to assign calculations to forms. i.e. sum(Billable hours) where client select is "Client_Table" to show total billable hours per clients.

I then need to multiply by the specific rate.

Sorry, kind of new to this.

Thanks

[jzwp11]

Welcome to the forum!

The table structure is the most important part of any relational database application. For the table structure to work properly, it must follow the rules of normalization. For an overview of normalization check out this site.

I am thinking that perhaps I need to combine everthing into one table and just assign values to the Fields( Not for clients table).

No, doing so would violate normalization rules

For these tables I have edited the individual field to assign values in a combo box or set default values (Which I cant seem to access in a form).

It sounds like you are using lookup (combo/list boxes) fields in your table. Even though Access has this capability, it is not recommended. This site has a list of problems that table-level lookups can cause.

Also, it is best not to have spaces or special characters in your table or field names. And also, make sure not to use reserved words as table or field names. This site has a list of those reserved words.

With all that said, can you provide more detail on the fields in each table? Something along these lines:

tblClient
-pkClientID primary key, autonumber
-txtFName (text field)
-txtLName (text field)

tblProjects
-pkProjectID primary key, autonumber
-fkClientID foreign key to tblClient, long integer number
-dteProject (project date)