Linking dates In reports

[Brian]

My brain has stopped working. I have a Monthly Report of Expenses and a
separate Monthly Report Of Income. Both derive their totals from separate
Tables & Queries. The final report is a joining of the 2 in order to produce
a basic P&L.
By combining bothe report queries into one I get (as they say) unexpected
results. I have fiddled around with the joining of the date fields and tested
all the various field formats (first, last etc) but no joy. If one or other
report has a "blank" month then the other column remains blank.

Do I need to join these queries at an earlier stage?

 

[akphidelt]

This is always a problem for me also. It's tough to do a join because if 1
table doesn't have it, it doesn't join like you said. What i have done in the
past is create a table that has all the dates on it.

Then create a query that uses the dates table as the left join field. So no
matter what every date will show up. Then you can create criterias of what
dates you want to show and stuff.

Hope that helps.

 

[Brian]

Thanks for responding. I was working towards the same conclusion but
struggling a little bit with the design of the "One Date" table. First
attempt I ended up with two combo boxes rather than a combined datasheet.

 

[akphidelt]

I usually use excel, create a column of 10 years of dates or whatever. Then
just copy and "paste append" that in to the access table.

Then i create the query and I link the dates from the 2 other tables with a
LEFT JOIN to the date table.

Underneath the date column I put in the critieria of dates that I want.
Usually this can be linked to a form so people can select custom dates and
things like that. Not sure how you will want to go about that.

 

[Brian]

Getting there slowly. Imported the excel table as suggested and created
query with the "Excel" Dates + InvoiceDates+ExpensesDate. Joined the INV &
EXP dates to the Excel but still can't get the right answers.
Datatsheet is OK with Excel+INv or Exel+Exp but not with both joined .
Perhaps I am misunderstanding the interpretaion of LEFT join. Do you mean a
relationship join or could I troubke you to clarify.
Thanks