Link to home
Start Free TrialLog in
Avatar of KnutsonBM
KnutsonBMFlag for United States of America

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..........Possible.........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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

KnutsonBM,

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
Avatar of KnutsonBM

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.
will do, almost there, had some issues...........
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
have you done a compact and repair ?
then zipped the file.
compact and repair took it from 129 MB to 3 lol

thanks
Copy-of-Queue-Time-Counts-and-Av.mdb
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.
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?
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
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.
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

Open in new window

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,12,13,14,15,16,17,18,19,20,21,22,23,24);
Sorry, there should not be a 24 in the list.
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,12,13,14,15,16,17,18,19,20,21,22,23);
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?
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,12,13,14,15,16,17,18,19,20,21,22,23);



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

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.
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??
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.
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
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,DateValue(a.[Stage 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].[Stage Arrival TS]),DateValue([a].[Stage Arrival TS])) And
DateAdd("h",Hour([a].[Stage Status TS]),DateValue([a].[Stage Status TS])));

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
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 ;-)
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
@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.
SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
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
<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!
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.