cannot figure it out. need help!!!

[Tracy]

Hi,

I m a sql novice.
want to write a query:
1st column: dates, from 1st day of the month to last day of the month, need
to show everyday
2nd column: total charges for the day
3rd column: total payment for the day
I use subqueries to write this, and 1st column would base on either
charges.date, or payment.date
here're problems
if the 1st column base on charges.date(payment.date), when there's no
charges(payment) on a day, but there's payment (charge), the whole row would
not show.
I know I should not depend on charges.date or payment.date, and i have no
control of daily activity. what else I could do?
it's there any other solutions? Thanks alot!!!!

 

[John Vinson]

Hi,

I m a sql novice.
want to write a query:
1st column: dates, from 1st day of the month to last day of the month, need
to show everyday
2nd column: total charges for the day
3rd column: total payment for the day
I use subqueries to write this, and 1st column would base on either
charges.date, or payment.date
here're problems
if the 1st column base on charges.date(payment.date), when there's no
charges(payment) on a day, but there's payment (charge), the whole row would
not show.
I know I should not depend on charges.date or payment.date, and i have no
control of daily activity. what else I could do?
it's there any other solutions? Thanks alot!!!!

If you need to see every day, with or without records, you need an
"alldates" table. Just create a table AllDates, with one date/time
field; fill it with one day per record for as many days as you need.
One easy way to do so is to open Excel, type a date in cell A1, the
next day's date in A2, and then use "Fill Down" to fill a few years'
worth of dates. Copy and paste this column into AllDates.

You can then create a Query joining AllDates to Charges, and also to
Payments; select each Join line and choose option 2 - "Show all
records in AllDates and matching records in <other table>".

Base your Report on this query and you'll see all dates, whether or
not there was activity on that date.

John W. Vinson[MVP]