KnutsonBM
asked on
Build Table with Functions??
I need to build a table in access based off of data from a query
The query has columns Called Arrival Hour, Arrival Date, Finish Hour, and Finish Date
I need to build a table that shows the last 21 days in the Left Hand Column (Dynamic)
Then have a column for each hour (0, 1, 2, 3, 4...23) with a "Count If" Type formula to populate each one....
is this something that is Impossible..........Possib le........ .Probable?
The formula would count if:
Arrival Date is Less than or equal to the Rows Date
and
Arrival Time is Less than the Time of the header in the column to the right
and
Finish Date is Greater than or equal to the Rows Date
and
Finish Time is Greater than or equal to the the Time of the header in the column to the right
-I know this is asking for a lot..........i can break the question up into multiple questions for more points if required........but i've hit a wall.......I can do this in excel no problem..........but it turns out that the requirement is in access.......fml
-Brandon
The query has columns Called Arrival Hour, Arrival Date, Finish Hour, and Finish Date
I need to build a table that shows the last 21 days in the Left Hand Column (Dynamic)
Then have a column for each hour (0, 1, 2, 3, 4...23) with a "Count If" Type formula to populate each one....
is this something that is Impossible..........Possib
The formula would count if:
Arrival Date is Less than or equal to the Rows Date
and
Arrival Time is Less than the Time of the header in the column to the right
and
Finish Date is Greater than or equal to the Rows Date
and
Finish Time is Greater than or equal to the the Time of the header in the column to the right
-I know this is asking for a lot..........i can break the question up into multiple questions for more points if required........but i've hit a wall.......I can do this in excel no problem..........but it turns out that the requirement is in access.......fml
-Brandon
ASKER
k, it may take me a few minutes to get rid of some data
Appreciate it if you could upload an mdb file. I only have A2003 on this machine.
ASKER
will do, almost there, had some issues...........
ASKER
I can not get the file size down??? any recommendations
I have a single table with just under 50,000 rows and 2 simple queries
I have a single table with just under 50,000 rows and 2 simple queries
have you done a compact and repair ?
then zipped the file.
then zipped the file.
ASKER
How big is it? Maybe just zip it and then upload? Alternatively, just keep enough records to demonstrate your point - that may be 10, 100, 1000.
ASKER
anybody making any headway?
@KnutsonBM
Is [State Status TS] the finish time?
What are the Type and [State Type] columns?
Does the [Initiation Line Number] column have an effect on your counts?
Is [State Status TS] the finish time?
What are the Type and [State Type] columns?
Does the [Initiation Line Number] column have an effect on your counts?
ASKER
The initiation Number will be filtered off in the Prod Loc and CS Query so no
Type and Stage Type will be filtered down in the C1 query, I am trying to count how many times per hour something is in a specific stage, i have a query for each different stage type, which from your efforts, i will duplicate it to each one of them
and yes, stage status TS is the finish time
Stage arrival ts is the start time
Type and Stage Type will be filtered down in the C1 query, I am trying to count how many times per hour something is in a specific stage, i have a query for each different stage type, which from your efforts, i will duplicate it to each one of them
and yes, stage status TS is the finish time
Stage arrival ts is the start time
I think the easiest approach to generating dates and times is to use a Tally table, as described in this article:
https://www.experts-exchange.com/A_5410.html
Then a Group By query should produce the counts.
https://www.experts-exchange.com/A_5410.html
Then a Group By query should produce the counts.
If we go back 21 days from today - 24 Sep, I see 11 records of type stage = 'C1' as shown below. I am at a loss as to how you want those records counted based on your description.
ID Initiation Line Number Stage Arrival TS Stage Status TS Type Stage Type
22495 46 9-24-2011 5:37 9-24-2011 5:38 C C1
22319 46 9-24-2011 5:44 9-24-2011 5:49 C C1
22552 46 9-24-2011 12:55 9-24-2011 13:58 C C1
22599 46 9-24-2011 14:12 9-24-2011 14:32 C C1
22587 46 9-24-2011 14:20 9-24-2011 14:51 C C1
38547 46 9-24-2011 14:44 9-24-2011 18:27 C C1
22238 46 9-24-2011 15:31 9-24-2011 16:17 C C1
22261 46 9-24-2011 16:36 9-24-2011 16:54 C C1
22574 46 9-24-2011 18:04 9-24-2011 18:09 C C1
38275 46 9-24-2011 20:23 9-24-2011 20:27 C C1
38277 46 9-24-2011 20:31 9-24-2011 20:38 C C1
Something like this?:
TRANSFORM Count a.[Stage Arrival TS]
SELECT a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
FROM [All EDP Stages] a
WHERE a.[Stage Status TS]>a.[Stage Arrival TS]
GROUP BY a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
PIVOT Hour([Stage Arrival TS]) IN (1,2,3,4,5,6,7,8,9,10,11,1 2,13,14,15 ,16,17,18, 19,20,21,2 2,23,24);
TRANSFORM Count a.[Stage Arrival TS]
SELECT a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
FROM [All EDP Stages] a
WHERE a.[Stage Status TS]>a.[Stage Arrival TS]
GROUP BY a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
PIVOT Hour([Stage Arrival TS]) IN (1,2,3,4,5,6,7,8,9,10,11,1
Sorry, there should not be a 24 in the list.
ASKER
Sytax Error (missing operator) in query expression 'Count a[Stage Arrival TS]'.
A couple of corrections:
TRANSFORM Count(Format(a.[Stage Arrival TS],"mm-dd-yyyy h"))
SELECT a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
FROM [All EDP Stages] a
WHERE a.[Stage Status TS]>a.[Stage Arrival TS]
GROUP BY a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
PIVOT Hour([Stage Arrival TS]) IN (1,2,3,4,5,6,7,8,9,10,11,1 2,13,14,15 ,16,17,18, 19,20,21,2 2,23);
TRANSFORM Count(Format(a.[Stage Arrival TS],"mm-dd-yyyy h"))
SELECT a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
FROM [All EDP Stages] a
WHERE a.[Stage Status TS]>a.[Stage Arrival TS]
GROUP BY a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
PIVOT Hour([Stage Arrival TS]) IN (1,2,3,4,5,6,7,8,9,10,11,1
ASKER
cool, that gets the table made, but i have questoins abou tthe WHERE Clause...
I am not looking for when the stge status TS is greater than the stage arrival TS
they should all be that way anyways
I am looking for when
Arrival Date is Less than or equal to the Rows Date
and
Arrival Time is Less than the Time of the header in the column to the right
and
Finish Date is Greater than or equal to the Rows Date
and
Finish Time is Greater than or equal to the the Time of the header in the column to the right
any thoughts?
I am not looking for when the stge status TS is greater than the stage arrival TS
they should all be that way anyways
I am looking for when
Arrival Date is Less than or equal to the Rows Date
and
Arrival Time is Less than the Time of the header in the column to the right
and
Finish Date is Greater than or equal to the Rows Date
and
Finish Time is Greater than or equal to the the Time of the header in the column to the right
any thoughts?
What do you mean by Rows Date?
Please corrrect the query by adding the alias for [Stage Arrival TS] - I've named it ArrivalDate
TRANSFORM Count(Format(a.[Stage Arrival TS],"mm-dd-yyyy h"))
SELECT a.[Initiation Line Number], DateValue(a.[Stage Arrival TS]) AS ArrivalDate
FROM [All EDP Stages] a
WHERE a.[Stage Status TS]>a.[Stage Arrival TS]
GROUP BY a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
PIVOT Hour([Stage Arrival TS]) IN (1,2,3,4,5,6,7,8,9,10,11,1 2,13,14,15 ,16,17,18, 19,20,21,2 2,23);
Please corrrect the query by adding the alias for [Stage Arrival TS] - I've named it ArrivalDate
TRANSFORM Count(Format(a.[Stage Arrival TS],"mm-dd-yyyy h"))
SELECT a.[Initiation Line Number], DateValue(a.[Stage Arrival TS]) AS ArrivalDate
FROM [All EDP Stages] a
WHERE a.[Stage Status TS]>a.[Stage Arrival TS]
GROUP BY a.[Initiation Line Number], DateValue(a.[Stage Arrival TS])
PIVOT Hour([Stage Arrival TS]) IN (1,2,3,4,5,6,7,8,9,10,11,1
ASKER
the Rows Date is the date in column 2
so the calculation i need for each cell is based off of the date of that row and the header of the column
so the calculation i need for each cell is based off of the date of that row and the header of the column
Limiting column2 to any Date of the past 21 days, in your recordset there must be hundreds or thousands of dates before any of the dates from 09-24-2011 to 10-15-2011. Where does that take you - better yet, what am I missing? I still don't see where you are coming from.
ASKER
so what is being counted is how many things were open in that queue in that particular hour
to determine what was open i have to count based off of those four criteria
count if the arrival date is less than or equal that that rows date
and if the arrival time is less than or equal to the rows header to the right
and if the finish date and time is after the date and time for that row header combination
so say something arrived at 1am on 8/25 and left at 3 am on 8/26
i need to be able to show that it was in that queue for each hour between 1am 8/25 and 3am 8/26
does that help clarify??
to determine what was open i have to count based off of those four criteria
count if the arrival date is less than or equal that that rows date
and if the arrival time is less than or equal to the rows header to the right
and if the finish date and time is after the date and time for that row header combination
so say something arrived at 1am on 8/25 and left at 3 am on 8/26
i need to be able to show that it was in that queue for each hour between 1am 8/25 and 3am 8/26
does that help clarify??
Yes, to be clear, something arriving at 1 and leaving at three we have a count of 1 under each of 1, 2, and 3 - right?
Each ID is a different Item?
I've got to shut this thing down for now but I'll be back tomorrow if you do not already have an answer.
Each ID is a different Item?
I've got to shut this thing down for now but I'll be back tomorrow if you do not already have an answer.
In this database you will find a [Q_27398521 solution] query that should give you the data you want.
Copy-of-Queue-Time-Counts-and-Av.mdb
Copy-of-Queue-Time-Counts-and-Av.mdb
KnutsonBM: I have this query that is supposed to create a records set consisting of all the hours between the arrival and status times for each record containing a date in the past 21 days. If I could just get it to run then creating the cross tab query to provide you your desired result is a snap. However, when I run the query, it appears to populate the record set ok, but after about 10 seconds I get a popup error message as depicted in the image below. I have spent too many hours over the past three days trying to figure out why. BTW, the small table Nums contains a single field Num with the integer values of 0 to 29 - 29 because at least one of your records starts on one day, in this case 10 Oct, and ends the next day 11 Oct. Why 29, the 29 will cater to records where the status time is up to 06:00 am the next day.
Perhaps you or someone else can find my flaw - it sure beats me! I thought this would be an elegant solution to the problem but I have been stopped dead in my tracks.
SELECT a.[Initiation Line Number], a.[Stage Arrival TS], a.[Stage Status TS],
DateAdd("h",Nums.Num,DateV alue(a.[St age Arrival TS])) AS DTHrsHere, a.[Stage Type] AS SType
FROM [All EPD Stages] AS a, Nums
WHERE (a.[Stage Type]="C1") And (DateValue(a.[Stage Arrival TS]) Between DateAdd("d",-21,Date()) And Date()) AND
(DateAdd("h",[Nums].[Num], DateValue( [a].[Stage Arrival TS])) Between
DateAdd("h",Hour([a].[Stag e Arrival TS]),DateValue([a].[Stage Arrival TS])) And
DateAdd("h",Hour([a].[Stag e Status TS]),DateValue([a].[Stage Status TS])));
Clipboard03.jpg
Perhaps you or someone else can find my flaw - it sure beats me! I thought this would be an elegant solution to the problem but I have been stopped dead in my tracks.
SELECT a.[Initiation Line Number], a.[Stage Arrival TS], a.[Stage Status TS],
DateAdd("h",Nums.Num,DateV
FROM [All EPD Stages] AS a, Nums
WHERE (a.[Stage Type]="C1") And (DateValue(a.[Stage Arrival TS]) Between DateAdd("d",-21,Date()) And Date()) AND
(DateAdd("h",[Nums].[Num],
DateAdd("h",Hour([a].[Stag
DateAdd("h",Hour([a].[Stag
Clipboard03.jpg
@KnutsonBM and @GRayL
Me being me, I attacked this with VBA code.
I built a parameter form that will take a status code and two dates.
These are used to pin the recordset down to what you'd like to see.
The VBA then walks through the resulting recordsets and adds a tally in each hour between the Arrival and Status time.
tblCounts then has the result
I think this is what you want.
Let me know
Queue.mdb
Me being me, I attacked this with VBA code.
I built a parameter form that will take a status code and two dates.
These are used to pin the recordset down to what you'd like to see.
The VBA then walks through the resulting recordsets and adds a tally in each hour between the Arrival and Status time.
tblCounts then has the result
I think this is what you want.
Let me know
Queue.mdb
KnutsonBM: Well, surprise of surprises - we have a few nulls in Stage Status TS, and a few records which bridge midnight which means the data counts for that record will be in two different records in a cross tab query. Anyway, I now have a solution - coming soon to a theater near you ;-)
ASKER
haha, you guys are pretty outstanding, i'm sorry i haven't been able to keep up with your progress, i've been SLAMMED today and will be for a few more hours, and at that point i'm going home and forgetting about work with a number of adult beverages, i will check in first thing in the morning, THANKS!
brandon
brandon
@GRayL
Unless I completely misunderstood the OP a record that went from 10 pm one day to 11 pm the next day would up the tally by one in 3 columns on one day and 23 columns on the next day.
It will be interesting to see how that can be attacked with SQL alone.
Unless I completely misunderstood the OP a record that went from 10 pm one day to 11 pm the next day would up the tally by one in 3 columns on one day and 23 columns on the next day.
It will be interesting to see how that can be attacked with SQL alone.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Something strange here, I renamed the file in my local machine by adding '2' to the name before '.mdb' before uploading. However, the uploaded name had been changed back to the original ???
Nick, I only added my comment so that KnutsonBM would understand that the data from one record may be split over two days.
KnutsonBM: If you have any situations where the span in hours is greater than 30 then you must expand the table Nums accordingly.
Shpeeking of aldut bevirages, enyoj ;-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Impressive to say the least...........
Please test and ensure that the accepted solutions do indeed return the correct data.
Looking at @GRayL's solution and mine, they did not return the exact same results consistently.
If you have results available in Excel, you may want to verify the Access results against them.
Looking at @GRayL's solution and mine, they did not return the exact same results consistently.
If you have results available in Excel, you may want to verify the Access results against them.
While you're at it, test mine.
Nick: Why are you selecting an existing arrival date rather than the requested 'in the last 21 Days'? The date criteria have nothing do do with actual arrival or status dates.
<Me being me>
I built something more generic with a parameter form.
Pick your status type and the end points of a timeframe and give 'er
From
<Type and Stage Type will be filtered down in the C1 query, I am trying to count how many times per hour something is in a specific stage, i have a query for each different stage type, which from your efforts, i will duplicate it to each one of them>
I inferred from that that a selectable Stage Type parameter would be desirable, and looking at it, I figured being able to pick your timeframe would be nice too.
I could have coded it for BETWEEN DATE() AND (DATE() - 21), too.
But then what if you wanted to view yesterday's results?
This was also nicer for testing purposes as I could select a single day and Stage Type and see if the results jived with what I would get by filtering the raw data.
That comes with the caveat that, since it's looking at arrival dates, anything that carried over from dates before the cutoff wouldn't tally
I was not quite certain why my results and yours aren't consistent, and everytime I got close to saying I thought yours weren't, I doubted mine a bit, instead.
I think my findfirst logic still wasn't quite right, and instead of erroring on NoMatch, it just doesn't navigate, hosing the result.
Hence my caution to the author to verify the result.
I think this has that bug sorted out and that my results will now match yours
Queue.mdb
I built something more generic with a parameter form.
Pick your status type and the end points of a timeframe and give 'er
From
<Type and Stage Type will be filtered down in the C1 query, I am trying to count how many times per hour something is in a specific stage, i have a query for each different stage type, which from your efforts, i will duplicate it to each one of them>
I inferred from that that a selectable Stage Type parameter would be desirable, and looking at it, I figured being able to pick your timeframe would be nice too.
I could have coded it for BETWEEN DATE() AND (DATE() - 21), too.
But then what if you wanted to view yesterday's results?
This was also nicer for testing purposes as I could select a single day and Stage Type and see if the results jived with what I would get by filtering the raw data.
That comes with the caveat that, since it's looking at arrival dates, anything that carried over from dates before the cutoff wouldn't tally
I was not quite certain why my results and yours aren't consistent, and everytime I got close to saying I thought yours weren't, I doubted mine a bit, instead.
I think my findfirst logic still wasn't quite right, and instead of erroring on NoMatch, it just doesn't navigate, hosing the result.
Hence my caution to the author to verify the result.
I think this has that bug sorted out and that my results will now match yours
Queue.mdb
Well, things are surprising to say the least. I created a query qryChkHrs which isolated the C1 records in the past 21 days which ran over one day and discovered several where the hrs span was nearing 200 - over 8 days! I then created a NumsAny table from 0 to 200 and reran the queries - with quite different results. The new mdb is uploaded below.
Copy-of-Queue-Time-Counts-and-Av.mdb
Copy-of-Queue-Time-Counts-and-Av.mdb
<grin>
This may be one case, providing the recordset stays small enough, that a VBA solution may prove more robust than a SQL one.
Because the VBA is looking at each record in a loop, incrementing the tally in each hour field between the Arrival time and 23:00 and then moving on to a new day, there is no arbitrary limit to the span between Arrival and Status.
</grin>
Although, looking more closely, you could take a DMax of HrsDur from qryChkHrs and feed that in as the parameter to qmtNumsAny. You could get a parameter form on the go for the parameters in qmtTempStages and some VBA for SetWarnings = false and you'd get the same result I had.
Interesting result. Where I did the tallying through VBA, you got yours through a cartesian product, filtered for nulls and counted, and then pivoted. I'd have never thought to do it that way. Mainly because I am an idiot when it comes to pivot queries and I know VBA recordsets inside and out.
Given that cartesian products are expensive things, but so are VBA loops, I wonder which solution would scale up better?
I learned something new.
Thanks Ray!
This may be one case, providing the recordset stays small enough, that a VBA solution may prove more robust than a SQL one.
Because the VBA is looking at each record in a loop, incrementing the tally in each hour field between the Arrival time and 23:00 and then moving on to a new day, there is no arbitrary limit to the span between Arrival and Status.
</grin>
Although, looking more closely, you could take a DMax of HrsDur from qryChkHrs and feed that in as the parameter to qmtNumsAny. You could get a parameter form on the go for the parameters in qmtTempStages and some VBA for SetWarnings = false and you'd get the same result I had.
Interesting result. Where I did the tallying through VBA, you got yours through a cartesian product, filtered for nulls and counted, and then pivoted. I'd have never thought to do it that way. Mainly because I am an idiot when it comes to pivot queries and I know VBA recordsets inside and out.
Given that cartesian products are expensive things, but so are VBA loops, I wonder which solution would scale up better?
I learned something new.
Thanks Ray!
It is not important that Num be anywhere near the Max no of hrs in any of the eligible spans, only that it be at least as big. If Num went from 0 to 1000, it would not make any difference in the processing speed of qmtStagesTemp, as the Engine is in charge of limiting records which match the criteria - not a loop. I noticed a bit of a delay (several seconds) to process qmtStagesTemp, however, qxtStagesTemp was nearly instantaneous. I think an index in [All EPD Stages] would help a lot if we were to scale up that table. A few years back I was running a query with 400k records in a table in which the process ran over 4 minutes. I added the index and the same query ran in about 8-9 seconds.
A sample file, cleansed of any confidential data, would be very helpful.
In any event, based on your description, it looks like something you could build reasonably easily with a crosstab query.
Patrick