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...

I tried all I could think of in Query Design Vew but I run out of options. This probably needs SQL statement, I am not much experienced with those....

You state, "When the item is out of warehouse, Date2 value get's update with that day value and Status becomes Out." This sounds to me like a structure problem as records, once entered, should not be changed. If you change them you have no 'history' of what has transpired. Your date information needs to be in a related table and each change should result in a new record not a change of record.

I think that once you normalize your structure that getting out the data you want will be much easier...

My 3 cents worth...

Jack

I would agree, that data structure is not ideal, however I can not change it. Data is coming from another non Access database and it will not be changed. That was one of the reasons I try to use Access so that I can take data from that external database and get what I need....

Are you not able to get the data from the external source in bits and pieces so you can save the data in a more normalized way? Can't your create the related dates table in your db and import the specific data from the external source into that table? With non-normalized data you are going to have to come up with lots of clever work-arounds so that you can display the data you want...

I have no idea if using a Query By Form method will work for you (it builds the SQL on the fly) or not, but you can take a look at it how to implement it here.

At this point I do not have a specific answer for you, but I do wish you good luck!!

hth,
Jack

How do you want to classify the Item that went In in Feb 08 and came Out in Feb 08? Is it In or Out?

In a result of a query I want to see a table of all years and periods with Item status as per period end. If item went In in Feb 08 and came Out in Feb 08, then it should not appear in a result at all.

Firstly, I should emphasizes that Jack's advice is still the correct solution if you have control how the Tables should be structured. What I proposed here is definitely a work-around for the situation you presented.

Secondly, I will be working with actual date values of [date1] and [date2], not the calculated "Month-Year" as you proposed since this is more efficient than the calculated values.

Steps:
1. Create a local Look-up Table [tlkpReportPeriod] with the field:

Needless to say that this Table is badly denormalized but I did say that this is a work-around.

Start as far back as you want and create one record for each month. For example, starting in Jan 07
with:

1 "2007-01" 01/Jan/2007 31/Jan/2007
....

2. I should have asked another question: I think your statement "... 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)" may not be incorrect since if the item comes In during the month (for 2nd part: and comes out after then end of the month), it is likely that you want to count this item.

I will work on the basis of "less than or equal" rather than "less than" as stated (easy to change once you see/understand the SQL).

3. The SQL String of the required Query "that would summarize items for every year&month, but only those items that where (are???) in" should be something like (***untested***):


Notes:
* The Field RepPerStart is probably not necessary with the above SQL but I leave it in the Lookp-up Table just in case the criteria need to be changed and this Field may be useful.
* You may need to change the criteria for [Status] as I don't know its data-type and assumed it to be of Text data-type.

4. If you need to create a Cross-Tab Query, you can use the above Query as the data-source for your Cross-Tab Query. You can combine all into one SQL String for the Cross-Tab also.

Thank you so much for such a detail reply. I will give it a try tomorrow (it is 1 a.m. in Norway now, to high risk of making mistake)

You're welcome ... Good lucks with your project ...

Post back to this thread if my proposed work-around doesn't work out ...

I think I have tried all possible scenarious now, but still no luck. I have first used your SQL for query and replaced table and field names to the ones I have. Got error message. I have then created table tlkpReportPeriod and put names for fields exactly as described above. I only needed to change some field names. Here is my adjusted SQL:

SELECT LU_RP.RepPerText, Sum(IT.[E])
FROM tlkpReportPeriod AS LU_RP INNER JOIN Sum_E_v1 AS IT ON LU_RP.RepPerEnd >= IT.[DateIn]
WHERE ( IT.[In_Out] = "In" )
OR ( ( IT.[In_Out] = "Perm Out" ) AND ( IT.[Action_Date] > LU_RP.RepPerEnd ) )
ORDER BY LU_RP.RepPerStart;

Error message I get says :"You tried to execute a query that does not invlude the specified expression 'RepPerText' as part of an aggregate function".

I have then replaced LU_RP in first line with actual table name and got following code:

SELECT tlkpReportPeriod.RepPerText, Sum(IT.[E])
FROM tlkpReportPeriod AS LU_RP INNER JOIN Sum_E_v1 AS IT ON LU_RP.RepPerEnd >= IT.[DateIn]
WHERE ( IT.[In_Out] = "In" )
OR ( ( IT.[In_Out] = "Perm Out" ) AND ( IT.[Action_Date] > LU_RP.RepPerEnd ) )

this time query asks me to enter value for tlkpReportPeriod.RepPerText, after which it gives me one line reply no matter what I enter(should be around 20 lines as that many periods I defined).

I also had to remove Order By line as it was causing same error, by the way. However, since the order was not critical to me at this stage, I though it was ok to remove it.

Try:

This looks better I still had to remove last line in code (ORDER BY LU_RP.RepPerStart) to make it give me result, but at least it worked. I will do manual check to see if conditions are respected, but I do believe this might be it

Perfect, exactly as I wanted, big thanks for spending time on this!!!!

You're welcome ... Glad we could help ...