loop with query dates

[Guest]

Tableâ€invoiceâ€

Field invoiceid number
“ invoicedate date/time
“ in quantity number
“ storedtocontainer text
Table “ issuesâ€
Field issueid number
“ issuedquantity numbr
“ issueddate date / time
table†adjustmentsâ€
field adjustment _date date/time
“ adjustedquantity number
“ storedcontainer text
“ remarks text
I would like to have an query like this as like an spread sheet for an full
month
Mydate = anydate of the month
Instead of writing the sql I tried, im just giving report I want

Openingbalence=sum((inquantity + adjustedquantity )- sum(issuedquantity))
fine it works for any one perticular date end
but how to have opening balence for eachday in a month of 30 days in one
qurery like

1/11/07 10000
2/11/07 22000
& so on till 30/11/07

Can any one advise please
balu

 

[John Spencer]

One problem here is that you need a value for each type for each date in the
range.

'This query would give you an Invoices sum for every date that there was a
transaction, but if there was no transaction on a specific date there would
be no record.
SELECT InvoiceDate
,(SELECT Sum(Invoice.[In quantity] )
FROM Invoices as I2
WHERE I2.InvoiceDate <=Invoices.InvoiceDate) as InvoiceSum
FROM Invoice
WHERE InvoiceDate Between #2007-11-01# and #2007-11-30#

You need an auxiliary table that has all the dates in it that you are
interested in
CalendarDates
Field: TheDate

Now build three queries that look something like the following. One query
for each of the three tables
SELECT CalendarDates.TheDate
,(SELECT Sum(Invoice.[In quantity] )
FROM Invoices as I2
WHERE I2.InvoiceDate <=CalendarDates.TheDate) as InvoiceSum
FROM CalendarDates LEFT JOIN Invoices
WHERE TheDate Between #2007-11-01# and #2007-11-30#

Finally, you can join the three queries together and do your calculations
for on-hand inventory.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..