Data Not Showing on Sub Form

[June7]

Now that I can see the query in Design view, I see a circular linking of 3 tables. Not good. Remove the link between PreviousYearTotals and Invoices2013. I also changed the join types and 0038S shows 3 records in the Exp Sum GA subform. However, I don't think the ExpTotal figures are correct. Note that expense code 91 is listed twice for site codes 0038S and 0272S also 92 for 0272S. This is because Invoices2013 has multiple records for each expense code that are joined to the 1 expense code record in MainSiteTable and the query also has to deal with the data from Previous year that is joined to MainSiteTable. The Previous data cause another unique record for grouping and therefore 2013 expense codes and totals are duplicated. As previously suggested, summarize the Invoices2013 records first then join that summary query to ExpenseCode table and also join the PreviousYear table to ExpenseCode table. Don't use grouping on the second query.




You are using nnz() function. I've never seen nnz(), just Nz(). However, it seems to work.

You have a table named Invoices2013. Does this mean you intend to have a table for Invoices2014, Invoices2015, etc? Recommend one table.


Why isn't SiteNumber set as primary key in MainSiteTable?

[keiath]

Hi June.

I use the nnz as i have a global function so that if there is no data in a field it (as it was returning an error message) this function allows me to ensure = no data = 0.00

yes it says 2013, but I agree it will be 1 table regardless of year as i want the data out put to roll over for current and previous years and i will rework query to include date range.

The site code is set as the primary key on the main table, it is the main key on most of them as this is the main source of control

[June7]

Did you try suggested query structure?

[keiath]

Yes but what ever i tried didnt work, the only way i can get it to display budgets and previous year data is to add an invoice in the invoice table with a 0.00 for the amount using ofc the correct exp code with is very messy and could easy be forgotten