Question on design
 

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


--
jhahes
------------------------------------------------------------------------
jhahes's Profile: http://www.excelforum.com/member.php...o&userid=23596
View this thread: http://www.excelforum.com/showthread...hreadid=378163

Question on design
 

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 structu

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=378163