Table Design Question

[shane_albrandt]

An Access newbie

I am a process server and I need to design a database form where I can enter the following information and print it out for invoices:

Business Name (My Business Name)
Invoice Number
Billed To (Company I am Serving For)
Date Served
Defendant Name
Case No
Skip
Court
Serve
Total (total of Skip, Court and Serve)
Grand Total (Total of the "Total")

The Skip, Court and Serve fields must all contain a drop down list which contains different amounts. I need to be able to add new amounts to each drop down list.

For Example:
If I am Serving for Company A - the skip charge might be $0.00, but when I work for Company B - the skip charge might be $4.00

The same goes with the Court and Serve fields.

I assume I need to create a table for all the companies, which contains the dollar amounts for Skip, Court and Serve?

The form must also contain several dollar amounts for each type of service.

Where do I start?

 

[theprez]

Tables to start with

Business Table
Your Business Nam
Adresss, etc.. (Can print on Invoice)

Customer Table
Customer Name (Key)
Customer Address
City
State
Customer Skip Charge (Used as default when customer is selected)
Customer Court Charge (Used as default when customer is selected)
Customer Serve Charge (Used as default when customer is selected)

Process Served Table
AutoKey (AutoNumbered)
Customer
Date
Defendant
Case
Skip
Court
Serve
Misc. Charges

Invoice Detail
AutoKey
Invoice Number
Customer Name
Process Servered (From Table)
Description

Invoice
Invoice Number
Customer Name
Other Charges

The Invoice form should have a subform with the invoice details.

Each detail line would have charges for serve, court, skip and Misc Charges

The detail lines totals would add together with Other Charges on the Invoice Form

This is quick and dirty, but I hope it helps. There may be other supporting tables or fields you may want to add, but this should get you started.

 

[robthedog]

I’d suggest you start off with 2 tables: one for company details, and one for invoices.

TblCompany details:

CompanyID
CompanyName
CompanyAddress
Skip (Store this with the individual company details)
etc.
ect.

TblInvoices

InvoiceNumber
BilledTo
CompanyID
CaseNo
Court
DateServed
DefendantName
Serve

You can then create a one to many relationship between CompanyID in the companyDetails table and the CompanyID in the Invoices table. Create queries to extract your data and calculate the totals only when you need them in reports etc.

What's a "process server"

Rob

 

[theprez]

A process server delivers court documents i.e. subpoenas, affadavits, etc..

 

[Kraj]

I strongly suggest you go to the library and find a book on the basics of Access or search online for a begginer's tutorial. You don't need to spend hours, but you do need a grasp of the very basics of designing a database; otherwise you'll have a hard time understanding the help provided here.

 

[shane_albrandt]

Ack!

That is crazy! LOL! I did design a database a LOOOOONG time ago (like 4 years) for a company and it was beautiful, I just forgot how I did it mostly. Anyone up to making just the basics for me, so I can get an idea, then I can populate it? he he he

-Shane

Tables to start with

Business Table
Your Business Nam
Adresss, etc.. (Can print on Invoice)

Customer Table
Customer Name (Key)
Customer Address
City
State
Customer Skip Charge (Used as default when customer is selected)
Customer Court Charge (Used as default when customer is selected)
Customer Serve Charge (Used as default when customer is selected)

Process Served Table
AutoKey (AutoNumbered)
Customer
Date
Defendant
Case
Skip
Court
Serve
Misc. Charges

Invoice Detail
AutoKey
Invoice Number
Customer Name
Process Servered (From Table)
Description

Invoice
Invoice Number
Customer Name
Other Charges

The Invoice form should have a subform with the invoice details.

Each detail line would have charges for serve, court, skip and Misc Charges

The detail lines totals would add together with Other Charges on the Invoice Form

This is quick and dirty, but I hope it helps. There may be other supporting tables or fields you may want to add, but this should get you started.

 

[theprez]

Not sure what you are asking here? You have the basics. You just have to get started and design the tables. What more help do you need? If you're asking someone to actually design the tables, not sure if anyone will do this without some compensation. I will offer any suggestions though. And also search this site for specific problem areas.