I have a table (or a result of anothe query) that I need to summarize based on few conditions and should be able to use result by Forms Reports and other queries.
My table has many fields but there are three that are important for conditional summary. Those are Date1; Date2 and Status. Let's add Amount to the list so that I have something to summarize. Date1 has date value of when item got in to warehouse. Date2 has also date value of when something happened to the item. Status has two values: In or Out. So when item arrives Date1 and Date2 get's that day value and item becomes In. The item can be moved in warehouse, but then only Date2 value will change. When the item is out of warehouse, Date2 value get's update with that day value and Status becomes Out. For this purpose let's assume that dates are Year&Month values (no days). So we have 12 dates for every year.
I want to get a query result that would summarize items for every year&month, but only those items that where in. This means, that for every year, month query should summarize items where (Date1 is less than date&month in that row AND Status is In) AND where (Date1 is less than date&month in that row AND Status is Out AND Date2 is more than year & month in that row).
I'll try to give an example if above is not clear. It is quite trivial that we should add all items that are in, but we also need to add items, where system has status out today, if we are summarizing items for January 08 and out date (date2) is Feb'08. This means that item was removed from warehouse in February, which means it was still in warehouse in January.
I can create a query to summarize values for every month, run it number of times (once for every month) and then add result of all queries into one database. But that is not fun.... First of all I would need to run it many times, then my result database would be growing a lot and third, it would not be smart solution...