Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form design question. | Excel Discussion (Misc queries) | |||
Best Practice - Design Question | Excel Discussion (Misc queries) | |||
design cross plattform question | Excel Discussion (Misc queries) | |||
spreadsheet design question | New Users to Excel | |||
Design Question | Excel Programming |