Simple But Complecated Problem Reports and Subtotals

[axg275]

Hi All,



I have been having a problem putting together a report. I have two tables. Table A that has unique entries for the same "Field". Table A has 170 rows. Table B that has that same "Field" but its a smaller table with 32 rows. I created a report base off query that joints Table A and Table B. When I join ofcorse table B entries are repeated for the same "Field". In the report "Detailed" section I add columns from the query from what used to be Table A. In the Subtotal I want to use what was in Table B. But it does not work since the numbers are repeated. I dont know what to do. I tried the DLookup and DSum. I thought maybe doing some sort of query. DLookup does not seem to work. I may not have correct syntax, iv tried several different ways and cant get it to work.

I am attaching the database. I am trying to get NGVU_Eng Footer to sum up entries. Take a look at the text box "Text101". I am trying to pull in data from qry_OilTable Column "Prob_01 Risked Oil Recovery (Mbbl)" summed up to NGVU level. which is a column in this same query called "NGVU_Eng".

Maybe someone has ran into this problem before and can give me a hand on solving this problem.

Thank you in advance for any help you can provide.


Alex

[jojowhite]

you forgot to attach the 2 workbooks.

[axg275]

Hi sorry, i am attaching the same file. I changed the linked tables to local tables.

[jojowhite]

there is a problem on attaching any zip file here (get corrupted when downloaded),
you can check in Design view of report the Control source of "text101".

you need to download the db from my Onedrive.
https://1drv.ms/u/c/c5af92cc292c1bb3...c-MTg?e=2dGbge

[Welshgasman]

zip files appear to work now arnel.

[June7]

Also, be aware that First and Last aggregate functions might not return what you expect. Possibly you should use Min instead of First.