• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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
0
KnutsonBM
Asked:
KnutsonBM
  • 19
  • 12
  • 6
  • +3
2 Solutions
 
Patrick MatthewsCommented:
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
0
 
KnutsonBMAuthor Commented:
k, it may take me a few minutes to get rid of some data
0
 
GRayLCommented:
Appreciate it if you could upload an mdb file.  I only have A2003 on this machine.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

thanks
Copy-of-Queue-Time-Counts-and-Av.mdb
0
 
GRayLCommented:
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.
0
 
KnutsonBMAuthor Commented:
anybody making any headway?
0
 
aikimarkCommented:
@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?
0
 
KnutsonBMAuthor Commented:
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
0
 
aikimarkCommented:
I think the easiest approach to generating dates and times is to use a Tally table, as described in this article:
http://www.experts-exchange.com/A_5410.html

Then a Group By query should produce the counts.
0
 
GRayLCommented:
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

0
 
GRayLCommented:
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);
0
 
GRayLCommented:
Sorry, there should not be a 24 in the list.
0
 
KnutsonBMAuthor Commented:
Sytax Error (missing operator) in query expression 'Count a[Stage Arrival TS]'.
0
 
GRayLCommented:
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);
0
 
KnutsonBMAuthor Commented:
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?
0
 
GRayLCommented:
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);



0
 
KnutsonBMAuthor Commented:
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

0
 
GRayLCommented:
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.
0
 
KnutsonBMAuthor Commented:
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??
0
 
GRayLCommented:
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.
0
 
aikimarkCommented:
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
0
 
GRayLCommented:
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
0
 
Nick67Commented:
@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
0
 
GRayLCommented:
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 ;-)
0
 
KnutsonBMAuthor Commented:
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
0
 
Nick67Commented:
@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.
0
 
GRayLCommented:
Here it comes.  It takes two queries.  First run the make table query qmtTempStageson your dataset.  Then run the cross tab query.  If you like you can call the two actions in code and get the result with a single entry. Copy-of-Queue-Time-Counts-and-Av.mdb
0
 
GRayLCommented:
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 ???
0
 
GRayLCommented:
Nick,  I only added my comment so that KnutsonBM would understand that the data from one record may be split over two days.
0
 
GRayLCommented:
KnutsonBM:  If you have any situations where the span in hours is greater than 30 then you must expand the table Nums accordingly.
0
 
GRayLCommented:
Shpeeking of aldut bevirages, enyoj ;-)
0
 
Nick67Commented:
Ok,

Disregard the first sample I (Nick67) posted.
It had some logic errors that I have gotten sorted out.
Looking at single days, I think I have may it right.


Queue.mdb
0
 
KnutsonBMAuthor Commented:
Impressive to say the least...........
0
 
Nick67Commented:
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.
0
 
aikimarkCommented:
While you're at it, test mine.
0
 
GRayLCommented:
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.
0
 
Nick67Commented:
<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
0
 
GRayLCommented:
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
0
 
Nick67Commented:
<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!
0
 
GRayLCommented:
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.  
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 19
  • 12
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now