A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Invoice database / report question.



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 07:18 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Invoice database / report question.

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

  #2  
Old March 19th, 2010, 01:07 AM posted to microsoft.public.access.tablesdbdesign
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Invoice database / report question.

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" wrote in message
...
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



  #3  
Old March 19th, 2010, 01:52 AM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Invoice database / report question.

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.,
  #4  
Old March 19th, 2010, 10:44 AM posted to microsoft.public.access.tablesdbdesign
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Invoice database / report question.

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" wrote in message
...
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.,



  #5  
Old March 19th, 2010, 11:30 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Invoice database / report question.

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" wrote in message
...
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.,


  #6  
Old March 19th, 2010, 02:04 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Invoice database / report question.

Dennis,

It's good to question your structure, because that it the foundation for
everything else. But defining your structure well requires an understanding
of the real-world process that you want to database, and the mission(s) that
you want the database to accomplish. From your post, it looks like you have
put a lot of goo thought into that area. However, since you have not
informed us enough on real-world process that you want to database, and the
mission(s) that you want the database to accomplish, we can't really review
your structure for you.

So, if you want us to analyze it, you'll need to tell us the above. If you
prefer to analyze it yourself, you might start by making a list of the
prospective entities to database. You might start those of your listed
tables:


- Work Orders
- whatever entity that second table is
- Work Order Details
- Expense Reports
- Expense Report Details

And consider a few other possible entities:

Invoices
Customers
Employees


You'll have to decide whether or not invoices are an entity (which heavy
duty systems treat them as) or not (which is how all 100% of the Access
examples I've seen treat them)

Make sure all of the data in each table is of "one-to-one" type with it's
entity. If not, split it.

See if the entities in any two tables are in a pure one-to-one relationship
with each other. If so, combine them into one table.

Well, there a few thoughts. Good luck.



  #7  
Old March 19th, 2010, 11:20 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Invoice database / report question.

Allen,

Allen,

I agree with your comment. Since this was both a database and report issue,
I originally tried to post to both group using the usenet address, but I
screwed up somehow.

Once Jeanette answered my questions, I want to know how to create a total
for invoice with different sub-forms. That is why I posted the question in
the report group.

But I guess I should have waited a day or so before I posted to the other
group.
You point is well taken.

Thanks again.

Dennis

"Allen Browne" wrote:

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" wrote in message
...
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.,


.

  #8  
Old March 20th, 2010, 06:41 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default Invoice database / report question.

Fred,

Thanks for the input. You are absolutely correct, I should have included a
description of the real world process. I will do that next time.

As Allen Browne stated, he answered the question in a post in the report
section. As stated in my first entry, I tried to post to two group (Database
and Report) because I felt this had issues for both group.

In case anyone else reads this, here is the link to the other question and
Allen's answer:


http://www.microsoft.com/communities...f-fd173f45d14c



Dennis



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.