Data Not Showing on Sub Form

[keiath]

Hi guys

I have this subform problem



I import all the invoices for the client

ID	Site Code	Invoice Date	PO	Invoice	Contractor	Invoice Amount	Expense Code	Exp Description	Lease Code	Status	A	B	C	D	E	F
771	0038S	14/05/2013	34795	MAY-LD1595	Living Decor	1000.00	65		A	Authorized on 20/05/2013 by LD, Paid on 24/05/2013	0.00	0.00	1000.00	0.00	0.00	0.00
772	0038S	30/09/2012	99276	9001399	Rouse Partners Llp	2880.00	91		A	Authorized on 06/03/2013 by MAB, Paid on 11/03/2013	0.00	0.00	2880.00	0.00	0.00	0.00

in another table I have previous Year Expenses and Budgets by Expense Code

ID	Site Code	Group Code	Expense Code	Expense Discription	Group Total	Budget
43	0038S	A	91	Accountant & Solicitors Fees	950.00	700.00
44	0038S	A	92	RCS Legal Fees	407.00	415.00
45	0038S	A	65	Repairs & Day to Day Maint	1523.00	3500.00
46	0038S	A	90	Ringley Management Fees	3837.00	4500.00

But on the Subform its only showing the group totals where there is an invoice for that expense code, and not showing Previous Years and Budgets where there is no expense in the current year

Expense Discription	Exp Total	P/Y Total	Budget
Repairs & Day to Day Maint	1000.00	1523.00	3500.00
Accountant & Solicitors Fees	2880.00	950.00	700.00

Any idea how I can correct this?

Thanks in Advance

[June7]

Do you still have prior raw invoice data in the database? Why save aggregate data to a table? Why not calculate when needed?

I presume you have a table of all expense codes? Create queries that summarize the expenses then join those queries to the all expense codes table.

[keiath]

The Invoice Table is an import of raw data, (which I need not just for the forms but for report to list all the invoices) no calculations are done here

The Previous year/Budget table is put together using drop down of the Group Code and Expense Code and then I enter the amount that's in the accounts for the previous year, so again no calculation

The subform is a Query that brings together the invoice table and previous year/budget table to the relevant Client Code.

Hope that makes sense

[June7]

You do not have prior year raw invoice data?

Customary practice is to save raw data and calculate summary data when needed. Archiving raw data and saving summary data might be required if data exceeds Access 2GB size limit.

Did you try the query I suggested?

[keiath]

No we don't always have previous year data, this might come from another accountant, and all we have is what is filed.

The queries are already linked to expense codes and group codes.

[June7]

Provide query SQL statement(s) for analysis.

Or provide the db. Follow instructions at bottom of my post.

[keiath]

SELECT [Invoices 2013].[Site Code] AS [Site Code], Sum([Invoices 2013].[Invoice Amount]) AS [SumOfInvoice Amount], [Invoices 2013].[Expense Code] AS [Exp Code], [Invoices 2013].[Lease Code] AS [Group Code], [Expense Codes].[Expense Discription], [Previous Year Totals Expenses].[Group Code], [Previous Year Totals Expenses].[Group Total], [Previous Year Totals Expenses].Budget
FROM ([Expense Codes] INNER JOIN [Invoices 2013] ON [Expense Codes].[Expense Code] = [Invoices 2013].[Expense Code]) INNER JOIN [Previous Year Totals Expenses] ON ([Expense Codes].[Expense Code] = [Previous Year Totals Expenses].[Expense Code]) AND ([Invoices 2013].[Site Code] = [Previous Year Totals Expenses].[Site Code])
GROUP BY [Invoices 2013].[Site Code], [Invoices 2013].[Expense Code], [Invoices 2013].[Lease Code], [Expense Codes].[Expense Discription], [Previous Year Totals Expenses].[Group Code], [Previous Year Totals Expenses].[Group Total], [Previous Year Totals Expenses].Budget
HAVING ((([Invoices 2013].[Lease Code])="A") AND (([Previous Year Totals Expenses].[Group Code])="A"));

[June7]

INNER JOIN requires related records in all datasets. Use LEFT or RIGHT ("Include all records from Expense Codes and only those from {other source} that match").

[keiath]

Hi June

Sorry was out for a while, I am not totally sure I understand what you mean by this?

[June7]

The query is using INNER JOIN to link the datasets. Need to use RIGHT or LEFT - just not sure which it will be. Do you know how to change the join type of the links in query designer?

[keiath]

Yes I changed the join type so it arrows left or right tried both it errors with the message ambiguous outer joins.

I tried it on all of them in the query box

[June7]

That query doesn't look like what I suggested.

I will have to review your db. If you want to provide, follow instructions at bottom of my post.

[Historypaul]

I think it's telling you that you have ambiguous outer joins because you are trying to join more than 2 tables in your query. I'm a rookie, so the simplest way I've found to fix this is to first join two of the tables in a query, then use that query in another query joining the last table. Watch your join types, making sure that you select "all" of the table you need and "only the matching records" from the other tables.

[June7]

It is possible to have more than two tables in a query with multiple outer joins, depends on how the tables are related. Just hard to interpret the sql, especially without knowing the db.

[keiath]

Hi June

Please find attached db

(Main Site Table)Form) search for site number 0038S as you can see despite the budget having 4 (Previous Year Totals Expenses)Table) lines its only showing 2 lines these have invoices and showing but the other 2 have no expenses in the current year but the budget still needs to show (Invoices Group A Totals)Query)

Hope you can help its pretty much the last piece in the jigsaw for this database

Thanks Again

Keiath