Invoice database / report question.
in [Tables]
|
From: Dennis on 18 Mar 2010 14:18 Hi, I'm having problems conceptualizing the correct structure for my database and/or invoice form. I think the solution lies in both the database structure and the approach to the invoice form. So I'm going to post in both forums. I'm not quite sure how to describe my issue because it is still a bit cloudy on my part. So if I leave something out, it is because I have not connected all of the dots yet. My goal is to create an invoice in Access XP. The invoice will include one or more line items for labor, parts, and expenses (mainly mileage, tolls, etc.) We do not maintain an inventory. We purchase the parts as needed from which ever vendor has the best price at that time. As a result, the parts table is a little different from your norm. Currently, I have the pieces, but I not quite sure how to put them together. My existing database (which I'm not sure is correct) is Work Order / Invoice Table - The work order is the created once a customer wants something done. Key Work Order # - automatic number Data Customer Number – FK to Customer Table Employee – FK to Employee Table Call Date – date field Resolution Date – date field Original Issue - text Resolution – text Note – note field Work Order Det Table - There is one record for each time entry. I'm also using this info to generate the Employee's Time Sheet Report. I also use this information as my notes of what was done on each job for each customer. Key WO Seq No – auto number Data Work Order number - FK to Work Order Table Work Data – Date – Work date Hours – decimal – hours in quarter hour increments. Issue – note field – what issue did the employee work on. Resolution – note field – how the issue was resolved. Expense Report Table: Key ExpRptNo – Automatic number Data Employee No – FK to Employee Expense From Dt – date Expense To Dt – dade Comments - Notes Exp Report Details Key Expense Seq No – auto number Data Work Order - FK to Work Order Table Expense Date – Date Expense Type – FK to Exp Type Table Milage – decimal Rate – milage rate Expense Amount Parts Table Key Item No – automatic assigned number. Data Work Order Number – FK to Work Order Table Vendor No – FK to Vendor Table Manufacturer – FK to Manufacturer table Model No – text Part Desc – text Quantity Price Taxes Shipping Warranty Time Period Notes My goal is to have all three items (labor, parts, expense) for a given Work Order / Invoice appear on a single invoice in some a reasonable order. Ideally, I would have “associated” items appear together. For example, if I have replace a power supply and a disk drive, I would like to have the labor, part cost, and any expenses for the power supply appear together and the have the labor, part, and any expense for the disk driver appear together and then have a total. Failing that, I could group my invoice by labor, parts, and expense using subforms. Not idea, but it would be ok. Any suggestions? Thanks, Dennis
From: Jeanette Cunningham on 18 Mar 2010 20:07 You will need subreports on a main report. The main report will be based on a work order. Each subreport will show details related to that work order. You will probably find it easier to show all the labour for a work order in one subreport and all the parts for the same work order in a different subreport. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:5BFE112A-E463-4A92-8859-4504EB3458E9(a)microsoft.com... > Hi, > > I'm having problems conceptualizing the correct structure for my database > and/or invoice form. I think the solution lies in both the database > structure and the approach to the invoice form. So I'm going to post in > both > forums. > > I'm not quite sure how to describe my issue because it is still a bit > cloudy > on my part. So if I leave something out, it is because I have not > connected > all of the dots yet. > > My goal is to create an invoice in Access XP. The invoice will include > one > or more line items for labor, parts, and expenses (mainly mileage, tolls, > etc.) We do not maintain an inventory. We purchase the parts as needed > from > which ever vendor has the best price at that time. As a result, the parts > table is a little different from your norm. > > Currently, I have the pieces, but I not quite sure how to put them > together. > > > My existing database (which I'm not sure is correct) is > > Work Order / Invoice Table - > The work order is the created once a customer wants something done. > > Key Work Order # - automatic number > Data Customer Number - FK to Customer Table > Employee - FK to Employee Table > Call Date - date field > Resolution Date - date field > Original Issue - text > Resolution - text > Note - note field > > > Work Order Det Table - > There is one record for each time entry. > I'm also using this info to generate the Employee's Time Sheet Report. > I also use this information as my notes of what was done on each job > for each customer. > > Key WO Seq No - auto number > Data Work Order number - FK to Work Order Table > Work Data - Date - Work date > Hours - decimal - hours in quarter hour increments. > Issue - note field - what issue did the employee work on. > Resolution - note field - how the issue was resolved. > > Expense Report Table: > Key ExpRptNo - Automatic number > Data Employee No - FK to Employee > Expense From Dt - date > Expense To Dt - dade > Comments - Notes > > > Exp Report Details > > Key Expense Seq No - auto number > Data Work Order - FK to Work Order Table > Expense Date - Date > Expense Type - FK to Exp Type Table > Milage - decimal > Rate - milage rate > Expense Amount > > > Parts Table > > Key Item No - automatic assigned number. > Data Work Order Number - FK to Work Order Table > Vendor No - FK to Vendor Table > Manufacturer - FK to Manufacturer table > Model No - text > Part Desc - text > Quantity > Price > Taxes > Shipping > Warranty Time Period > Notes > > My goal is to have all three items (labor, parts, expense) for a given > Work > Order / Invoice appear on a single invoice in some a reasonable order. > Ideally, I would have "associated" items appear together. For example, if > I > have replace a power supply and a disk drive, I would like to have the > labor, > part cost, and any expenses for the power supply appear together and the > have > the labor, part, and any expense for the disk driver appear together and > then > have a total. > > Failing that, I could group my invoice by labor, parts, and expense using > subforms. Not idea, but it would be ok. > > > Any suggestions? > > > Thanks, > > Dennis >
From: Dennis on 18 Mar 2010 20:52 Jeanette, Thanks. Is the database structure ok? I wasn't sure if I was having problems the database structure or the report. When I finally wrote out my questions, I thought about the sub-report you suggested. But I was not sure if my conceptualization issue is with my db structure of my vison of the invoice. Thanks for you advice. Dennis.,
From: Jeanette Cunningham on 19 Mar 2010 05:44 The table structure works fine - I can see that you have a separate table for each entity in the database. That stucture is similar to an inventory database that I have developed. In the invoice report for that database, I have one main report with 8 sub reports. There is a sub report with the business name, another with business contact details and many more to deal with all the other details and calculations needed on the invoice. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:2B1E08A6-3385-45B6-95EA-AD40339A9C4B(a)microsoft.com... > Jeanette, > > Thanks. > > Is the database structure ok? I wasn't sure if I was having problems the > database structure or the report. When I finally wrote out my questions, > I > thought about the sub-report you suggested. But I was not sure if my > conceptualization issue is with my db structure of my vison of the > invoice. > > Thanks for you advice. > > Dennis.,
From: Allen Browne on 19 Mar 2010 06:30 Dennis, as Jeannette says, you can do what you need with this structure. In another group where you posted the same question, I suggested another structure for you. One of the problems with posting the same question to multiple groups is that your responses get fragmented, i.e. it limits the way the responses flow for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Dennis" <Dennis(a)discussions.microsoft.com> wrote in message news:2B1E08A6-3385-45B6-95EA-AD40339A9C4B(a)microsoft.com... > Jeanette, > > Thanks. > > Is the database structure ok? I wasn't sure if I was having problems the > database structure or the report. When I finally wrote out my questions, > I > thought about the sub-report you suggested. But I was not sure if my > conceptualization issue is with my db structure of my vison of the > invoice. > > Thanks for you advice. > > Dennis.,
|
Next
|
Last
Pages: 1 2 Prev: Change blanks to 0 (zero) Next: Navigation Pane unhides when creating liked table |