Question on design

[jhahes]

I have a general question on designing a database.


Right now I have this on sheet 1

Agent Name - a1
Agent Office - b1
Agent Cell - c1
Job Type....d1
Item 1 qty.....e1
Item 2 qty.....f1
item 3 qty
item 4 qty
item 5 qty
item 6 qty


I really don't know how to use a pivot table, but will it work for invoicing and reporting.

I would appreciate any help

Thanks
Josh

[Ron Coderre]

I'll take a shot at this one, but I'll have to be a little bit blunt here.

What you described is not a database table, but a whole bunch of data that will be difficult to report against. What would you do if a customer orders 25 items? Will you expand your information to include items through Item25?

If you want to set the stage for database functionality, you might consider starting with some typical basic database structure:

First, select a sheet that will contain all of your lookup tables and build these on it:

LU_Agent table
A1:AgentID
B1:Agent Name
C1:Agent Office
D1:Agent Cell

LU_JobType table
F1:JobType

LU_Job table
H1:JobID
I1:JobType (validated by the LU_JobType list)
J1:AgentID (validated by the LU_Agent list)

LU_Items table
L1:Item
M1:Price

Then, on your input sheet, you might consider using these Headings:
A1:JobID (validated by the LU_Job list)
B1:JobType (vlookup on the LU_Job list)
B1:ItemID (validated by the LU_Item list)
C1:Item (vlookup on the LU_Item list)
D1:Quantity (entered)
E1:Price (vlookup on the LU_Item list)
F1:ExtendedCost (Calculated: Quantity x Price)

For reporting, you could add additional calculated items to the right of those ranges, or build a reporting table on another sheet, referencing the input items and the LU lists.

Then, you'll be in a position to use a pivot table to report on jobs, agents, items, prices,....whatever.

Sure, it's a little bit of upfront work, but you can reap tremendous efficiencies if there will be any kind of volume in your inputs.

Note: That isn't everything you need to know about normalizing a database structure (not by a long shot), but hopefully it will get you on the right track.

I hope that helps.

Regards,
Ron