Automatic Invoicing in Access 1

[monkeysee]

Currently we are manually entering annual invoices for 300+ food licenses. The invoice is using 3 different tables:

1) Food: with fields FacilityID; Priority; Status
(Priority dictates the risk factor and fee level)
2) Invoice Table: InvoiceID; FacilityID, Billing Date; FeeID
3) Fees: FeeID, FeeAmount; FeeType; Inactive

I will want to invoice all facilities with the Priority of "1"; and a status of "active"
FeeID would be ID #19; FeeType: "License 1"

I have started with an append query:

INSERT INTO Invoice ( FacilityID )
SELECT Food.FacilityID
FROM Food
WHERE (((Food.Priority)="1") AND ((Food.Status)="active"));

how do I incorporate the other tables to include the FeeID or the FeeType?

 

[Andrzejek]

I would assume in your Invoice Table InvoiceID field is a Primary Key, so you do not have to worry about it in the Insert statement (right?)
So, you statement will start with:
[tt]
INSERT INTO Invoice (FacilityID, Billing Date, FeeID)[/tt]

Now, you need to create a Select statement that will get {just) these 3 fields from your other tables. Concentrate on this task.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[monkeysee]

Thank you Andy

Yes, the invoiceID is a primary key

This is what I have now:

INSERT INTO Invoice ( FacilityID, FeeID )
SELECT Qry_InvoiceAuto.Food.FacilityID, Qry_InvoiceAuto.FeeID
FROM Qry_InvoiceAuto
WHERE (((Qry_InvoiceAuto.Status)="Active") AND ((Qry_InvoiceAuto.Priority)="1"));

This works EXCEPT, since there are over 25 different fees in the Fees table, I only want one specific fee to be used. How would I get that accomplished?

Deb

 

[Andrzejek]

Getting a little crazy with the '(' and ')', are we...?
And since you are going after just one query, you may as well do:
[tt]
SELECT FacilityID, FeeID
FROM Qry_InvoiceAuto
WHERE Status = "Active" AND Priority = "1"[/tt]

(are you sure the Priority field is text and not a number?)

Now you just need to add a Fees table to your Select to get the one specific fee

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[monkeysee]

Thanks Andy!

Ok, I think we are getting somewhere. I now have this:

INSERT INTO Invoice ( FacilityID, FeeID )
SELECT Qry_InvoiceAuto.Food.FacilityID, Qry_InvoiceAuto.[Fees.FeeID]
FROM Qry_InvoiceAuto INNER JOIN Fees ON Qry_InvoiceAuto.[Invoice.FeeID]=Fees.FeeID
WHERE (((Fees.FeeID)="19") AND ((Qry_InvoiceAuto.Status)="Active") AND ((Qry_InvoiceAuto.Priority)="1"));

It works, but the datasheet returns no records. I should have 200+ records showing with the new invoice. What am I missing?

PS: I really do appreciate your insight!!!!

 

[Andrzejek]

Don't do the INSERT yet. Concentrate on your Select statement. And if you expect to get 234 records inserted, make sure your Select returns 234 records first:

[PRE]
SELECT QIA.FacilityID, QIA.FeeID
FROM Qry_InvoiceAuto QIA INNER JOIN Fees ON QIA.FeeID = Fees.FeeID
WHERE Fees.FeeID = "19"
AND QIA.Status = "Active"
AND QIA.Priority = "1"[/PRE]

(I just re-formatted your statement without any changes. You do not need [] around your fields unless you use spaces or reserved words in the names of your fields. Also, I like to use aliases, they make statements a lot shorter/easier to read, IMO)

Is your Fees.FeeID a character field? Should be numeric in my opinion...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[monkeysee]

THANK YOU SO MUCH ANDY !!!!

I got it to work