Query for a daily summary

  • Thread starter Thread starter Michael Wong
  • Start date Start date
M

Michael Wong

Hi,

I want to consolidate multiple table into one unique query, but have some
difficulties with the query...

I would like a query that returns the following results:
- DailyDate (whenever there is a date in one of the tables)
- SumOfJournalAmount (for the given date)
- SumOfPaidInvoice (for the given date)
- SumOfOtherTables

The tables are (simplified):
TCashierJournal (CashierJournalID [PK], JournalDate, CashierId, Amount)
TPaidInvoice (PaidInvoiceId [PK], PaidDate, InvoiceNo, Amount)
And other table with a date and Amount.

The difficulty I have is for the date which might have in one table but not
the other.

Any suggestion would be much appreciated.
Thank you.

Michael
 
Hi,


Make a query like:

SELECT JournalDate As TheDate FROM TCashierJournal
UNION
SELECT PaidDate FROM TPaidInvoice
UNION
SELECT Date FROM otherTable


Save it under, say, the name QU1.


SELECT ...
FROM ((QU1 LEFT JOIN TCashierJournal ON
QU1.TheDate=TCashierJournal.JournalDate)
LEFT JOIN TPaidInvoice ON QU1.TheDate=TPaidInvoice.PaidDate)
LEFT JOIN otherDate ON QU1.TheDate=otherTable.Date

GROUP BY Qu1.TheDate




Hoping it may help,
Vanderghast, Access MVP
 
Michel,

I think that's what I needed. Will try it tonight.

Thank you very much.

Michel Walsh said:
Hi,


Make a query like:

SELECT JournalDate As TheDate FROM TCashierJournal
UNION
SELECT PaidDate FROM TPaidInvoice
UNION
SELECT Date FROM otherTable


Save it under, say, the name QU1.


SELECT ...
FROM ((QU1 LEFT JOIN TCashierJournal ON
QU1.TheDate=TCashierJournal.JournalDate)
LEFT JOIN TPaidInvoice ON QU1.TheDate=TPaidInvoice.PaidDate)
LEFT JOIN otherDate ON QU1.TheDate=otherTable.Date

GROUP BY Qu1.TheDate




Hoping it may help,
Vanderghast, Access MVP



Michael Wong said:
Hi,

I want to consolidate multiple table into one unique query, but have some
difficulties with the query...

I would like a query that returns the following results:
- DailyDate (whenever there is a date in one of the tables)
- SumOfJournalAmount (for the given date)
- SumOfPaidInvoice (for the given date)
- SumOfOtherTables

The tables are (simplified):
TCashierJournal (CashierJournalID [PK], JournalDate, CashierId, Amount)
TPaidInvoice (PaidInvoiceId [PK], PaidDate, InvoiceNo, Amount)
And other table with a date and Amount.

The difficulty I have is for the date which might have in one table but not
the other.

Any suggestion would be much appreciated.
Thank you.

Michael
Click to expand...
Click to expand...
 
Back
Top