Annual Invoice Updating

[monkeysee]

I have a database of contractors whose license comes up for renewal each november. I currently have a NAZ table for each contractor which also indicates the type of contractor of each.

A second table has the invoice details: date billed, type of fee, date paid.

The third table has the fee categories and the amounts of each.

What I want to do is to be able add to each invoice record with a new date (in this case 10/31/04) with the appropriate fee AND keep the history of previous invoices. (I know I can do this individualy but would like to automate the process)

I have tried the update query and it writes over the information already there

This is got to be simple - but I just can't figure it out.

Any help would be appreciated (also, I have tried to search for answer on the forum but the search function is not working)

 

[PHV]

I have tried the update query
And what about the append query ?
You may have to use temp tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

You would only use an update or append query if you wanted to permanently change the tables' contents. I suspect that you just want to know how much each auditor billed on a given day. You will need to construct a select query to obtain the data.

Perhaps you could send us your table definitions in this format:

table name: Inventory
fieldname/field definition/data type/primary key?
type/The type of item/text/yes
amount/ the amount of the item/integer/no
price /The unit price/currency/no
unit/ the unit name/text/no

After we get some table definitions, we should be able to help you.

 

[OhioSteve]

I am the author of the last post. I don't know why the last post did not include my user ID.

 

[JonFer]

Cloaking device?

 

[lespaul]

No, I think he's become the invisible man!!!

Leslie

 

[monkeysee]

OhioSteve
Just now getting back to my problem. What I want to do is to automatically bill my contractors their annual license fee. This fee is due just once a year (November 30).

1st TAble: Contractor
ContractorID (primarykey)
License# (text)
Street (text)
City (text)
Zip (text)
ContractorType (text)[Installer, Pumper, LandApply]

2nd Table: Invoice
InvoiceID (primarykey)
ContractorID (number)
InvoiceDate (date)
PaidDate (date)
AmountPaid (currency)

3rd Table: Fees
FeeID (primarykey)
FeeType (text)
FeeAmount (currency)

4th Table: Billing Details
InvoiceDetailsID (primarykey)
InvoiceID(number)
FeeID (number)

My Query for this is named: QryInvContractor

What I want to do, is to generate an annual invoice for each contractor automatically each year for their different types of licenses without having to individually enter the invoice on each record.

I hope I'm making sense.

 

[OhioSteve]

I apologize for not responding sooner...I didn't see that you had added another post. You have given us alot of information. This is a good starting point.

I assume that one contractor can have many invoices, and that one invoice can have many billing details. AmountPaid.Invoice seems to represent their payments. That stil leaves a few questions:

1) What field represents the amount of their bill?
2) How does the table "fees" relate to the other tables?
3) You say that a contractor can have more than one license, but your schema does not allow for that.

My gut reaction is that you need something like this structure:

Contractors Table (a list of contractors)
ContractorID (pk)
[other fields]

license Table (a list of license types)
licenseID (pk)
license fee
renewal date
[other fields]

license junction table (pk is license id + contractor id)
license ID
contractor ID
license number

Contractor Payments Table
paymentID
contractor ID
payment amount
[other fields?]

One contractor can have many licenses, and a license can be held by many contractors. So that is a textbook many-to-many relationship. Its SO clear that it really does sound like something from a textbook. Thus you need the textbook solution: a junction table. The junction table's key is a compound key. Its components are the two foreign keys. I assume that each combination of a license type and a contractor has a unique license number, so I stuck that into the junction table. I also assume that the contractors' licenses might expire at different times. That's why I put renewal date into the junction table.

I made contractor payments the child of contractor. You could also argue that it should be the child of the junction table. I assume that the contractors CAN make partial payments. That's why you need a separate table for their payments