Is this query possible?

M

Michael

I have a table TInvoice: InvoiceId, InvoiveDate, Amount
And a second one TCashierJournal: CashierJournalId, CashierId,
CashierJournalDate, CashierAmount

I wonder if it's possible to resume the both tables in one query, by date.

The situation is the following: on each day, there can be 0 or more invoices
and the same day, and the same day, there are 0 or more cashiers working.

I have already tried with several queries already but couldn't get what I
wanted:

QCashierDaily:
SELECT TCashierJournal.CashierJournalDate, Sum(TCashierJournal.Amount) AS
DailyCashier
FROM TCashierJournal
GROUP BY TCashierJournal.CashierJournalDate
ORDER BY TCashierJournal.CashierJournalDate;

QInvoiceDaily:
SELECT TInvoice.InvoiceDate, Sum(TInvoice.Amount) AS DailyInvoice
FROM TInvoice
GROUP BY TInvoice.InvoiceDate
ORDER BY TInvoice.InvoiceDate;

QWorkday:
SELECT QDailyInvoice.InvoiceDate AS Workday
FROM QDailyInvoice UNION SELECT QDailyCashier.CashierJournalDate FROM
QDailyCashier;

And finally the query I am trying to get working:

QDailyIncome
SELECT QWorkday.Workday, QDailyInvoice.DailyInvoice,
QDailyCashier.DailyCashier
FROM QWorkday, QDailyInvoice, QDailyCashier
WHERE (([InvoiceDate]=[Workday])) AND ([CashierJournalDate]=[Workday]));

The problem I am getting is that I don't get any daily income when there is
no invoice on that day although there is a cashier journal
 
P

Phil

Hi, I am not a MVP but just a normal users. I do not
think I can give you an answer but I can share what I
think. According to your query, you are using the normal
query which means that you will only see a date when the
date is existed in TInvoice and Tcashierjournal. I think
you may be able to find an answer or hint from the left
join query. I am not sure. It could be called right join
query. It will force to show a date even though there is
no transaction in TInvoice but TCashierJournal.

Hoepfully, it will help. Thank you for your time.



-----Original Message-----
I have a table TInvoice: InvoiceId, InvoiveDate, Amount
And a second one TCashierJournal: CashierJournalId, CashierId,
CashierJournalDate, CashierAmount

I wonder if it's possible to resume the both tables in one query, by date.

The situation is the following: on each day, there can be 0 or more invoices
and the same day, and the same day, there are 0 or more cashiers working.

I have already tried with several queries already but couldn't get what I
wanted:

QCashierDaily:
SELECT TCashierJournal.CashierJournalDate, Sum (TCashierJournal.Amount) AS
DailyCashier
FROM TCashierJournal
GROUP BY TCashierJournal.CashierJournalDate
ORDER BY TCashierJournal.CashierJournalDate;

QInvoiceDaily:
SELECT TInvoice.InvoiceDate, Sum(TInvoice.Amount) AS DailyInvoice
FROM TInvoice
GROUP BY TInvoice.InvoiceDate
ORDER BY TInvoice.InvoiceDate;

QWorkday:
SELECT QDailyInvoice.InvoiceDate AS Workday
FROM QDailyInvoice UNION SELECT
Click to expand...
QDailyCashier.CashierJournalDate FROM
QDailyCashier;

And finally the query I am trying to get working:

QDailyIncome
SELECT QWorkday.Workday, QDailyInvoice.DailyInvoice,
QDailyCashier.DailyCashier
FROM QWorkday, QDailyInvoice, QDailyCashier
WHERE (([InvoiceDate]=[Workday])) AND ([CashierJournalDate]=[Workday]));

The problem I am getting is that I don't get any daily income when there is
no invoice on that day although there is a cashier journal


.
Click to expand...
 
J

Jeff Boyce

Michel

I'm not sure I understand what you're trying to do, but I did notice that I
could not see an obvious field that would join your two tables.

I don't understand "what" you're trying to do -- you've described "how"
you're trying to do something.

More info, please...

Jeff Boyce
<Access MVP>
 
Top