normalisation 2

[kattz]

I have the following data in a spreadsheet which i need to place in an existing database. In my existing database, i already do the invoice required and have the rates for the various charges, i need to create a form that my boss can enter the data of no of working days, overtime shift hours and number of units blocked. When i have that data - i need to be able to go to my invoice form, create the invoice then have the invoice number place in the appropriate place relating to the charge. Then i need to be able to print various reports. My problem is trying to normalise the data.

The spreadsheet has the following in columns (not in rows as shown here) and data
COLUMN HEADINGS ROW DATA
Week ending 4/4/04
Working days 2
Overtime Shift Hrs 14
Fixed Charged $16795
invoice No 2272
Variable Charge $6992
Invoice No 2291
Overtime Recovery $2317
Invoice No 2292
Number of units blocked 93
Cost recovery $451.05
Invoice no 2293

As i said before i have all the rates to work out the cost charges in my invoice set up but what i had to do before was do it in access and then transfer the data to excel.

can anyone give me any ideas on how to set up the tables? I have tried a
tblCharges
Fixed Charge
variable Charge
Overime Recovery
Cost Recovery

tbl Management Cost
Id
Week ending
Working Days
Overtime Hrs
Charge (lookup table of tblCharges)
Invoice No
Number of Units blocked.

But this didn't work as i could only pick one charge per row in my form.

Help please

 

[Crowley16]

er, your data doesn't seem to be very clear...

in your spreadsheet, you seem to have multiple invoiceNo for each record... so I'm assuming a 1-many relationship between invoiceNo and weekEnding

so, how's about trying something like:

tblInvoices (PKey(InvoiceNo), FKey(WeekEnding), varCharge, fixCharge, overtime...)

tblManagement (PKey(WeekEnding), wkDays, OT, blocked...)

 

[lespaul]

Have you read 'The Fundamentals of Relational Database Design'. It will help a lot in understanding how to go through the normalization process.

HTH
Leslie

 

[willir]

Kattz

Another kick at the can...

tblManagementCost
CostID - primary key (pk)
WeekEndDate - date
DaysWorked - numeric, interger
OTShiftHrs - interger
UnitsBlocked - interger

Discussion:
Capture general detail on cost. I am guessing on OT Shift hrs and Units blocked. Are these associated with each WeekEndDate?

tblCostCode
CostCode - pk, character, see discussion
CostName
CostRate - numeric, money
CostUnit

Discussion:
This is your pricing or cost table. Allows you to set and change rates.
I opted for a CostCode because it is more intuitive than a CostID number.
The rate set here will be used as the default. Examples:

CostCode CostRate CostUnit
FIXED 16795.00 each
VCRG 499.43 HR
OTREC 165.50 HR
COSTREC 451.01 each

tblCostDetail
CostDetaildID - pk
CostID - foreign key (fk) to tblManagementCost
CostCode - fk to tblCostCode
CostRate - default from tblCostCode, can be over written
Units - number of units - hrs, one time, etc
ExtendedCost - CostRate * Units
InvoiceNo - not sure where this comes from, but you reference it several times


Discussion:
This table provides the details for the Management cost for the specific WeekEndDate.

You will have to tweak the design. Read the link provided by Leslie - good stuff.

Richard

 

[kattz]

thanks guys

I have read the 'fundamentals' it has helped in that i know now what is normalisation but it hasn't 'clicked' yet on how i can make my tables normalised. I find it a little difficult to understand because whenever i read anything on normalisation it is always about customers and orders - you don't see another percpective. Anyway, i'll keep plugging away and i will get it eventually i'm sure This forum has helped me so much with my many databases as i am a self taught Access user.

You might be able to help with another database i have - Human Resources and payroll.

What i have is an employee table and a time card table (very simplified here)

I have a form where i enter the start time and end time of every day and it works out hours worked, overtime, meal money etc.

apparantly my time card table is not normalised as i have fields named, mon start mon end, tue start, tue end etc.

In my form i have a lot of formulas with a lot of IIF's and Nz expressions as there are so many variables and now my query is way too complex. Can you give me any idea on how i should set up my time card table.

I have posted my sqls thread701-870076 and am trying what they have said but am still having troubles.

empid
start time
end time

and then using a cross tab query - but i don't understand how they work.

does anyone know of a sample database with something similar - i only need a head start then i can normally work it out on my own

 

[kattz]

Hey Willr

Robert
hope you still have this linked. your feedback has been amazing in getting me to understand normalisation.

the database that is was putting the management table in wasn't normalised and now i am in the process of normalising it.

i have done the tables as you have said plus added some of my own

Clients Tbl
Client acc (pk)
client name
client contact
address

PurchaseOrder tbl
purchase order no (pk)
description

ItemNo tbl
item no (pk)
purchase order no (fk)
description

CostCode tbl
Cost code (pk)
cost name
cost rate
unit

CostDetail tble
Cost detail (pk)
cost id (fk)
cost code (fk)
cost rate - lookup to cost rate table
units
invoice number


CostIdentification tbl (previously called tblManagementCost)
cost id (pk)
weekend date
variable hours wkd
otim hrs
units blocked
vehicle preserved
p&o vehicles moved
avis vehicles moved
nz units blocked
adhoc hours


what i need now is an invoice created out of all of the tables - do i create a specific table or do i just use a query to put all the fields i need from the current tables - i then need to have calculation fields gstexc, gst, gst inc and totals etc.

i then have to use a form to enter the data - print that off to give to the client and export to excel to send off to my head office to enter into the clients account.

 

[willir]

Thanks for the star Kattz

Your design looks much, much better. You may want to lookup my post on Costing.

A few pointers...
- Avoid using spaces in your table and field names. Otherwise, your SQL statements in the future will be more cumbersome, and more prone to errors. This is why "we" use mixed case. AdHocHours vs. adhoc hours, CostID vs cost id, etc.
- Avoid special characters when naming tables and fields. Ampersand (&) is used to "join" text strings, octophorp / pound / number sign (#) is used to encapsulate date fields.
- Try to be consistent - hr, hrs or hours but not a mixture; No, Num or Number but not a mixture. This simplifies coding later on.

Next step...
Work out your queries with test data. Make sure they work, and give you the results you want. It is much harder to address a database design later after you have created your forms and reports.

You may want to pay close attention to your Cost tables to see if they work the way you want them to.

Also, I am still not sure how you want to tie-in invoices to the cost detail table.

...Moving on - Invoicing
I would recommend that you track invoices on two new tables.

tblInvoice
InvoiceNo - primary key
ClientAcc - foreign key to client table
PurchaseOrderNo - foreign key to purchase order table
InvoiceDate

tblInvoiceDetail
InvoiceDetailID - primary key
InvoiceNo - foreign key to Invoice master table
CostCode - foreign key to Cost table
UnitQty - number of units
CostRate - currency, defaults from CostCode table
ExtendedCost - currency, UnitQty*CostRate

...Now I am wondering if the CostDetail table and InvoiceDetail table are the same thing.

Why use an Invoice table?
Well, your prices will change. Therefore, the invoice needs to store the price, and allow the end user to over-ride.

Richard

 

[kattz]

thanks Richard (sorry i got your name wrong before)

I was also thinking the CostDetail is really an InvoiceDetail.
Just a few more questions

if i make the CostDetail the InvoiceDetail - i just have to do a query between the two Invoice tables to do my form?

Each purchase order has a number of items eg afternoon shift, day shift, night shift which have to go on the same invoice, so along with PurchaseOrderNo i would have to put ItemNo in the InvoiceTbl?

You have been a fantastic help. when i finish this one i am going to start on my payroll one so you will be hearing from me again i'm sure that one is going to be heaps more complicated.

 

[willir]

Yea, I was wondering about the PO thing. PO's are a querky thing in that a company may not use them, use them as guide lines or use them without exception.

You can relate / link the PO in several ways -- my first post, I used a simple approach. A PO is assigned to one or more invoices. In a past life, when I purchased things, depending on availability and supplier, I could get numerous invoices covered for one PO. I was / we were not concerned about which invoice iteams were covered by which specific PO detail item.

If you are going to track this, you may consider...
- Adding a PODetailID field to the InvoiceDetail record
- Create another table that suports a many-to-many relationship...

tblPOInvoiceXRef
XRefID - primary key
PODetailID
InvoiceDetailID

I normally do not include an independent ID for my "join" tables, but I suspect you can have situations where one item is short-shipped, and therefore invoiced for the intial shipment and later when the short-ship issue is rectified.

Richard

 

[lespaul]

Have a star on me Richard, nice explanation of everything!

Ever finish Naked Empire?

les

 

[willir]

Leslie
Yep. And my son has read it three times already. I thought the the philosophical discussion was pretty good, and very applicable to current life today.

Thanks for the star.

And kudos back to you. Your posts keep getting better and better. I hope your platypus hunt is going well - awsome project which I am sure chews up your time.

Richard

 

[kattz]

i would just like to say a HUGE thanks to Richard (Willir) for the valuable advice he has given me - for the very first time i have a database which is totally normalised and works the way i always wanted it to. The guys at work are sick and tired of hearing me talk to the computer and now they hear a lot of cheering going on. (i don't know if that's any better though )

i will give you another star but here is a as well

thanks again, you will be hearing from me more when i start on my other databases.

Michelle

 

[willir]

Ah gee, thanks. I am glad it worked out.