Select Query for append query 1

[monkeysee]

My Select Query reads:

SELECT Food.FacilityID, License.LicenseID, Food.Priority, Food.Status, Fees.FeeID
FROM Fees RIGHT JOIN ((Food INNER JOIN License ON Food.FacilityID = License.FacilityID) INNER JOIN Invoice ON Food.FacilityID = Invoice.FacilityLUID) ON Fees.FeeID = Invoice.FeeID
WHERE (((Food.FacilityID)=14) AND ((Food.Priority)=1) AND ((Food.Status)="active") AND ((Fees.FeeID)=111));

My goal is to convert this to an append query that will had the new invoice for 2022.

The Select Query returns 6 records which would be an invoice for each year for the last 6 years. If I convert it to the append query it adds 6 new invoices instead of just the one needed for 2022

What am I doing wrong?

 

[Andrzejek]

Looks to me you need to modify your SELECT statement to return "just the one [record] needed for 2022"

---- Andy

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

 

[monkeysee]

Hi Andy!

The 2022 invoice does not yet exist in the table. I want to use an append query to add the 2022 invoice.

The end result I want to add a 2022 invoice to all existing facilities using the append query.

 

[Andrzejek]

The Select Query returns 6 records which would be an invoice for each year for the last 6 years.

Are those 6 records with the same data? If so, and you want just one record, you may try:[tt]

SELECT DISTINCT Food.FacilityID, ...[/tt]

Could you show the outcome of this Select statement here?

---- Andy

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

 

[Andrzejek]

BTW - I hate to compare hard-coded values, especially text, in my SQLs.
So, consider:[pre]
...
WHERE Food.FacilityID = 14
AND Food.Priority = 1
AND [blue]LCase[/blue](Food.Status) = "[blue]active[/blue]"
AND Fees.FeeID = 111

or

...
WHERE Food.FacilityID = 14
AND Food.Priority = 1
AND [blue]UCase[/blue](Food.Status) = "[blue]ACTIVE[/blue]"
AND Fees.FeeID = 111[/pre]

But a better (normalized) way of keeping this information would be to have another table (FoodStatus?)
[pre]
ID Status
0 New
1 Active
2 Spoiled
3 Rotten
[/pre]
And refer to FoodStatus.ID in your statement(s) instead.


---- Andy

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

 

[monkeysee]

SELECT DISTINCT Food.FacilityID, ...

Could you show the outcome of this Select statement here?

Results:

FacilityID = 14​

LicenseID = 13​

Priority = 1​

Status = Active​

FeeID = 111​

It returned the one record !!!!

I think the "DISTINCT" wording did it!

Thank you again! You are a life saver

 

[Andrzejek]

Great!
What about my other suggestions....

Greetings from Ames.

---- Andy

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

 

[monkeysee]

Good Morning Andy!

I like your suggestions as it makes the sql easier to read. However, when I use your format, access reverts it back.

I am now attempting to use the same append query to append invoices for all "Priority 1" facilities. The query we now have works only if the facility already had a FeeID of 111. Which is exactly what it is suppose to do according to the sql. BUT I want to append the '111' fee to all active priority 1's. So if a facility never had been billed previously f0r 111, they are not getting updated. I know I need to remove the criteria of 111 from the 'where' clause. But how do I bring the 111 to the insert clause?

Thanks again for all your insight

Deb

 

[Andrzejek]

when I use your format, access reverts it back

Interesting... So, who is in charge? You or Access?

how do I bring the 111 to the insert clause?

Could you show your SELECT statement? And state where the 'missing' value should be?
And if the 'fee' is missing, what (default) value do you want to have?

---- Andy

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

 

[monkeysee]

Hi Andy!
This is my issue: The Fee Table has 100+ different fees. The specific feeID that I want to populate the FeeID field is: 111

​

How do I accomplish that?​

If I put "111" in the criteria line, then it will only append records to records that has had existing FeeID 111 in the records history. It ignores any record that does not yet have a FeeID # and it ignores any record that may have had a different FeeID, but the record now requires FeeID 111. Hope this make sense.

Here is my Full Statement:


INSERT INTO Invoice ( FacilityID, FeeID )
SELECT DISTINCT Food.FacilityID, Fees.FeeID
FROM ((Food INNER JOIN License ON Food.FacilityID = License.FacilityID) INNER JOIN Invoice ON Food.FacilityID = Invoice.FacilityID) LEFT JOIN Fees ON Invoice.FeeID = Fees.FeeID
WHERE (((Food.Status)="Active") AND ((License.Prefix) Not Like "M") AND ((Fees.Inactive)=False));

 

[Andrzejek]

If you want to 'force' 111 into [tt]Invoice.FeeID[/tt], you can simply do:

[pre]
INSERT INTO Invoice ( FacilityID, FeeID )
SELECT DISTINCT Food.FacilityID, [highlight #FCE94F]111[/highlight]
FROM ((Food INNER JOIN License ON Food.FacilityID = License.FacilityID)
INNER JOIN Invoice ON Food.FacilityID = Invoice.FacilityID)
LEFT JOIN Fees ON Invoice.FeeID = Fees.FeeID
WHERE (((Food.Status)="Active")
AND ((License.Prefix) Not Like "M")
AND ((Fees.Inactive)=False));[/pre]

Your Select statement may become a lot shorter since you may not need Fees table...
Or, you can set [tt]Invoice.FeeID[/tt] default value to 111 and omit it in your Insert statement all together.

---- Andy

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

 

[Andrzejek]

It may be just me, but this:[tt]
...
AND ((Fees.Inactive)=False))[/tt]
looks weird to me...
You have a [tt]Fees[/tt] table, and in it a field named [tt]Inactive[/tt] that can be either True or False.

It takes me some brain gymnastics to figure out if True means 'the Fee is active' or 'the Fee is not active'? Or is it False
I would rather prefer to have a field named IsActive but that just may be me...

---- Andy

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

 

[monkeysee]

Hi Andy

I think the 'force' idea is what I need and it appears to be working.

Thanks again for all you help!!!