Union Not up dating Correctly

[keiath]

Hi Guys

I have two Queries that are union so when I up date 1 table it then unions 2 queries - works great

The Invoice Query (which is a list of invoice has two "changeable" fields Group Code and Exp Code), the exp code is not a problem but the group code is. When i set up the query in the criteria i had "A" so that it only shows invoice in group "A"

My problem is this when a change on the Group code happens its still showing in the query:-

Accruals which can have more than 1 Group Code

Site No	ID	Date of Invoice	PO	Invoice Number	Expr1005	SumOfAmount	Expense Code	Expense Discription	Group Code	Comments	A	B	C	D	E	F
0002S	32	23/04/2014		456		1020.20	65	Repairs & Day to Day Maint	A		0.00	1020.20	0.00	0.00	0.00	0.00
0002S	27					523.25	78	Communal Heating System	B		0.00	523.25	0.00	0.00	0.00	0.00
0038S	31						92	RCS Legal Fees	A		0.00	0.00	0.00	0.00	0.00	0.00

But he invoice union query should only show invoices in Group Code "A"

ID	Site Code	Invoice Date	Invoice	Contractor	PO	Group Code	Exp Code	Expense Discription	Invoice Amount	Status	A	B	C	D	E	F
27	0002S					B	78	Communal Heating System	£523.25		0	523.25	0	0	0	0
32	0002S	23/04/2014	456			A	65	Repairs & Day to Day Maint	£1,020.20		0	1020.2	0	0	0	0
861	0038S	09/01/2013	7150	Jaggar Support Services	98280	A	65	Repairs & Day to Day Maint	£145.20	Authorized on 10/01/2013 by KF, Paid on 14/01/2013	0	0	145.2	0	0	0
853	0038S	31/01/2013	3276	Ttm Limited	34933	A	54	Alarms/Access/Security/Entryph	£142.80	Authorized on 06/02/2013 by KF, Paid on 11/02/2013	0	0	142.8	0	0	0

This is what I have:-

SELECT [Invoices 2013].ID, [Invoices 2013].[Site Code] AS [Site Code], [Invoices 2013].[Invoice Date], [Invoices 2013].PO, [Invoices 2013].Invoice, [Invoices 2013].Contractor, [Invoices 2013].[Invoice Amount], [Invoices 2013].[Expense Code] AS [Exp Code], [Expense Codes].[Expense Discription], [Invoices 2013].[Lease Code] AS [Group Code], [Invoices 2013].Status, [Invoices 2013].A, [Invoices 2013].B, [Invoices 2013].C, [Invoices 2013].D, [Invoices 2013].E, [Invoices 2013].F
FROM [Expense Codes] INNER JOIN [Invoices 2013] ON [Expense Codes].[Expense Code] = [Invoices 2013].[Expense Code]
WHERE ((([Invoices 2013].[Lease Code])="A"))
ORDER BY [Invoices 2013].[Expense Code]
UNION SELECT Accruals.ID, Accruals.[Site No], Accruals.[Date of Invoice], Accruals.PO, Accruals.[Invoice Number], Accruals.Comments, Sum(Accruals.Amount) AS SumOfAmount, [Expense Codes].[Expense Code], [Expense Codes].[Expense Discription], Accruals.[Group Code], Accruals.Comments, Accruals.A, Accruals.B, Accruals.C, Accruals.D, Accruals.E, Accruals.F
FROM [Expense Codes] INNER JOIN Accruals ON [Expense Codes].[Expense Code] = Accruals.[Expense Code]
GROUP BY Accruals.ID, Accruals.[Site No], Accruals.[Date of Invoice], Accruals.PO, Accruals.[Invoice Number], Accruals.Comments, [Expense Codes].[Expense Code], [Expense Codes].[Expense Discription], Accruals.[Group Code], Accruals.Comments, Accruals.A, Accruals.B, Accruals.C, Accruals.D, Accruals.E, Accruals.F;

Any help would be greatly appreciated

Keith

[June7]

The Invoices SELECT has filter for Lease Code.

The Accruals SELECT is joining only to Expense Codes table and there is no filtering of the Accruals so Accruals will still show the "B" record, regardless of value in either Group Code field.

[keiath]

I change it and added into the field on both the Group Code from the Group Code table

Site Code	Invoice Date	Invoice	Contractor	PO	Group Code	Exp Code	Expense Discription	Invoice Amount	ID	Status	A	B	C	D	E	F
0002S					B	78	Communal Heating System	£523.25	27		0	523.25	0	0	0	0
0002S	23/04/2014	456			A	65	Repairs & Day to Day Maint	£1,020.20	32		0	1020.2	0	0	0	0
0038S	09/01/2013	7150	Jaggar Support Services	98280	A	65	Repairs & Day to Day Maint	£145.20	861	Authorized on 10/01/2013 by KF, Paid on 14/01/2013	0	0	145.2	0	0	0

SELECT [Invoices 2013].ID, [Invoices 2013].[Site Code] AS [Site Code], [Invoices 2013].[Invoice Date], [Invoices 2013].PO, [Invoices 2013].Invoice, [Invoices 2013].Contractor, [Invoices 2013].[Invoice Amount], [Invoices 2013].[Expense Code] AS [Exp Code], [Expense Codes].[Expense Discription], [Group Codes].[Group Code], [Invoices 2013].Status, [Invoices 2013].A, [Invoices 2013].B, [Invoices 2013].C, [Invoices 2013].D, [Invoices 2013].E, [Invoices 2013].F
FROM ([Expense Codes] INNER JOIN [Invoices 2013] ON [Expense Codes].[Expense Code] = [Invoices 2013].[Expense Code]) INNER JOIN [Group Codes] ON [Invoices 2013].[Lease Code] = [Group Codes].[Group Code]
WHERE ((([Group Codes].[Group Code])="A"))
ORDER BY [Invoices 2013].[Expense Code]
UNION
SELECT Accruals.ID, Accruals.[Site No], Accruals.[Date of Invoice], Accruals.PO, Accruals.[Invoice Number], Accruals.Comments, Sum(Accruals.Amount) AS SumOfAmount, [Expense Codes].[Expense Code], [Expense Codes].[Expense Discription], [Group Codes].[Group Code], Accruals.Comments, Accruals.A, Accruals.B, Accruals.C, Accruals.D, Accruals.E, Accruals.F
FROM [Group Codes] INNER JOIN ([Expense Codes] INNER JOIN Accruals ON [Expense Codes].[Expense Code] = Accruals.[Expense Code]) ON [Group Codes].[Group Code] = Accruals.[Group Code]
GROUP BY Accruals.ID, Accruals.[Site No], Accruals.[Date of Invoice], Accruals.PO, Accruals.[Invoice Number], [Expense Codes].[Expense Code], [Expense Codes].[Expense Discription], [Group Codes].[Group Code], Accruals.Comments, Accruals.A, Accruals.B, Accruals.C, Accruals.D, Accruals.E, Accruals.F, Accruals.Comments;

but as you can see its still showing B

[June7]

All you did was create a join to Group Codes table. This does nothing to eliminate "B" records, just makes related info from Group Codes records available.

Again, as far as I can tell Accruals SELECT does not have filter criteria and is therefore not being filtered. The filter would be on one of the grouping fields, so that is a HAVING clause:

HAVING [Group Code] = "A"

Can probably take the Group Codes table back out, just to simplify.

[keiath]

Hi June

Yes that would work of course, the problem in this case is that the accountant works on accruals and decides then which Group, this is why its unfiltered, plus its needed for other parts of the forms and balance sheet as the group is not relevant.

But the invoices are relevant and must be in the correct Group.

Thats the problem I am having other wise I would have to have up to 3 subforms of accruals so it filters to the correct invoice group (the invoices are in 3 subforms)

[June7]

Can't have both ways at the same time. Filter or don't filter.

Use the UNION as source for a dynamic parameter query for one purpose, use it as source for another query without parameter.