Need help to create a query with a Row number
I need a some help with the following query.
I was trying to use the DCount at the start of the query to count how many entries each day will have so I can then use the count in a cross tab query that will end up looking something like the following. Some days may have multiple entries, others may have one or none. Trouble is I can't use DCount because I can't get the unique values I need from just tblJobDetails because one of the criteria I need is in a linked table. The only way I can think of is to write a function that will give me the progressive count and use that in place of the DCount. However that will use a lot of overhead so before I do it I thought I would see if there is a more professional approach.
Seq Mon Tue Wed ...
1 a b c
2 d e
3 f
SELECT DisplaySeq: DCount("Activity","tblJobD
"##" AS SupplierIde, "TBA" AS Supplier, tblJobs.Jobnum, tblJobDetails.daterequeste
FROM (tblJobs LEFT JOIN tblSupervisors ON tblJobs.fkSupervisorId = tblSupervisors.id) RIGHT JOIN (tblSuppliers RIGHT JOIN (tblJobDetails LEFT JOIN tblActivities ON tblJobDetails.fkActivityId
WHERE (((tblActivities.IncludeIn
ORDER BY tblJobDetails.DateRequeste
I was trying to use the DCount at the start of the query to count how many entries each day will have so I can then use the count in a cross tab query that will end up looking something like the following. Some days may have multiple entries, others may have one or none. Trouble is I can't use DCount because I can't get the unique values I need from just tblJobDetails because one of the criteria I need is in a linked table. The only way I can think of is to write a function that will give me the progressive count and use that in place of the DCount. However that will use a lot of overhead so before I do it I thought I would see if there is a more professional approach.
Seq Mon Tue Wed ...
1 a b c
2 d e
3 f
SELECT DisplaySeq: DCount("Activity","tblJobD
"##" AS SupplierIde, "TBA" AS Supplier, tblJobs.Jobnum, tblJobDetails.daterequeste
FROM (tblJobs LEFT JOIN tblSupervisors ON tblJobs.fkSupervisorId = tblSupervisors.id) RIGHT JOIN (tblSuppliers RIGHT JOIN (tblJobDetails LEFT JOIN tblActivities ON tblJobDetails.fkActivityId
WHERE (((tblActivities.IncludeIn
ORDER BY tblJobDetails.DateRequeste
And just to add a bit; using any of the domain functions inside a SQL statement is a big no-no. They all simply represent an SQL Statement and there's no reason not to write that directly as Pat suggested.
The "no-no" part is that because the SQL logic is wrapped up in a function, the query parser can do nothing to optimize it, so your guaranteeing yourself poor performance.
Jim.
The "no-no" part is that because the SQL logic is wrapped up in a function, the query parser can do nothing to optimize it, so your guaranteeing yourself poor performance.
Jim.
BTW, the other part of that is that in using a domain function, you're forcing the use of what's called a sub-query, that is a query that is run once for each row.
Quite often and as Pat showed, there are better ways to get to the end goal.
Jim.
Quite often and as Pat showed, there are better ways to get to the end goal.
Jim.
Essentially, each domain function runs a separate query so if your main query returns a thousand rows, then it is going to run that domain function 1,000!!!!! times. The query engine is intelligent however, and if it figures out that running the function once will suffice, that is what it will do but most times the domain function is correlated with the main query and so it is different each time.
You will ALWAYS get better performance using the technique I proposed. The only time I ever use domain functions in queries is if the join technique will render the query not updateable and I am using the query as the recordsource for a form. In that case, my recordset is very limited anyway (we don't bind forms directly to tables or to queries without criteria) and so the domain function won't cripple the query.
You will ALWAYS get better performance using the technique I proposed. The only time I ever use domain functions in queries is if the join technique will render the query not updateable and I am using the query as the recordsource for a form. In that case, my recordset is very limited anyway (we don't bind forms directly to tables or to queries without criteria) and so the domain function won't cripple the query.
ASKER
Thanks for your enlightening comments. They all make sense but I'm still not sure how I turn that into the crosstab query I need. If I merge the output from the suggested query with my dataset in a cross tab I will only get one row per day showing how many records there should be, wont I? Let's say I have 3 records for Monday. Using the proposed method and linking it to my other tables I will only get one record with a record count of 3. I need Monday to have 3 records in the resultant crosstab, a record for count =1, another for count =2 and another for count =3. So for the following data:
Sep 17 A
Sep 17 B
Sep 17 C
Sep 18 E
Sep 19 D
I need to get to
Count Wed Thu Fri
1 A E F
2 B
3 C
Sep 17 A
Sep 17 B
Sep 17 C
Sep 18 E
Sep 19 D
I need to get to
Count Wed Thu Fri
1 A E F
2 B
3 C
ASKER
Sorry, where I said Monday above I meant Wednesday
Joining to the count query will get you an additional column. Each row will have the count for that day.
3 Sep 17 A
3 Sep 17 B
3 Sep 17 C
1 Sep 18 E
1 Sep 19 D
I need to get to
Count Wed Thu Fri
1 A E F
2 B
3 C
3 Sep 17 A
3 Sep 17 B
3 Sep 17 C
1 Sep 18 E
1 Sep 19 D
I need to get to
Count Wed Thu Fri
1 A E F
2 B
3 C
3 Sep 17 A
3 Sep 17 B
3 Sep 17 C
1 Sep 18 E
1 Sep 19 D
I need to get to
Count Wed Thu Fri
1 A E F
2 B
3 C ASKER
I'm having a really bad mental block here. If each row for a day has the same count then how do I do a cross tab that will keep all rows? Don't I need a unique column to do the cross tab on? I'm sure my question sounds dumb but I just don't get the concept.
The THREE "3" rows in the base query will end up as ONE "3" row when you run the cross tab. Just try it.
ASKER
But I need all 3 rows.
ASKER
I have to quit for tonight but I will try to create a mini sample database of what I need tomorrow
ASKER
I've attached a very compact version of what I am trying to do. This uses the DCount in the crosstab that I am trying to replace with a more efficient option. Any suggestions would be welcome.
Temp.accdb
Temp.accdb
I can't download the database. There is a problem with the site. They all download as htm which is useless. Tech support here can't tell me what the problem is so until I figure it out, there are no downloads in my future.
Did you try what I suggested? Please post the results and tell us what is wrong.
Did you try what I suggested? Please post the results and tell us what is wrong.
@Pat,
I sent you a PM and we'll see if we can't get to the bottom of it. There is nothing wrong with the site that I'm aware of.
@Rob
I'll download it here and have a look, but it may be later tonight before I get back to you (got a busy day on my hands)
Jim.
I sent you a PM and we'll see if we can't get to the bottom of it. There is nothing wrong with the site that I'm aware of.
@Rob
I'll download it here and have a look, but it may be later tonight before I get back to you (got a busy day on my hands)
Jim.
ASKER
Hi Pat and Jim, I appreciate any help you can give and recognise that your time is precious. I've reloaded the file, this time as a zip in case there was something wrong with the original.
I would have tried your suggestion but really wasn't sure how to implement it. The query I have created in the same show how it is meant to look in principle and I am not sure how to make it work any other way.
Temp.zip
I would have tried your suggestion but really wasn't sure how to implement it. The query I have created in the same show how it is meant to look in principle and I am not sure how to make it work any other way.
Temp.zip
ASKER
Hi, Just wondering if you managed to download the file?
I can download zip and the first fine here....just have gotten side tracked.
I'll look at it now.
Jim.
I'll look at it now.
Jim.
OK, finially got a look at this and am a little confused on exactly what it is your looking for. In the test DB, table one contains 8 recs for 9/29, 1 for 9/30, 2 for 10/1, and 1 for 10/3. This is the output I'm seeing now:
What's the end output your looking for?
Jim.
What's the end output your looking for?
Jim.
ASKER
This is a calendar type view. Jobs are assigned to teams as the work comes in and there is a variance in the amount of work each day. The operator needs to be able to see, in a single view, a summary of the jobs for the week. At present they're just entering a summary in a spreadsheet and working from that but I want to convert that to a database to make the process more seamless I have cut that down into the attached demo.
Rather than show all the fields and data I have in the actual database I have simplified it dramatically. What it's intended to show is a summary of the jobs that are assigned each day. So far in my demo there are 8 jobs on Monday, one each on Tuesday and Friday, one on Wednesday and none on Thursday. Once I know how to build the underlying query that builds the view they want I can apply it to the actual database.
So if you could help me figure out a more efficient way to build the display that will solve my problem.
Rather than show all the fields and data I have in the actual database I have simplified it dramatically. What it's intended to show is a summary of the jobs that are assigned each day. So far in my demo there are 8 jobs on Monday, one each on Tuesday and Friday, one on Wednesday and none on Thursday. Once I know how to build the underlying query that builds the view they want I can apply it to the actual database.
So if you could help me figure out a more efficient way to build the display that will solve my problem.
Rob,
Sorry, but it's still unclear as to what the output is you want.
The examples you gave don't seem to jive with your last comment and I don't understand the output. Given what you've described, I would think you would want to see:
Seq Mon Tue Wed Thu Fri Sat Sun
1 5
2 2
3 1 1
That is, those days with the most number of jobs shown first (Monday only shows 5 because of the true/false flag in the sample data - Monday actually has 8 jobs).
But even that doesn't make sense really. If your just trying to show a summary of the week, then why not simply:
Mon Tue Wed Thu Fri Sat Sun
5 1 2 1
unless your just trying to make the highest to lowest stand out. To add a little bit more, here's the count of jobs by date:
and here's the number of jobs by date, showing the first job for that date:
and finally your re-worked crosstab showing what I suggested above:
Note that there is no seq column (1,2,3,4), but the days with the most jobs are shown first, followed by the days with fewer jobs.
Jim.
Sorry, but it's still unclear as to what the output is you want.
The examples you gave don't seem to jive with your last comment and I don't understand the output. Given what you've described, I would think you would want to see:
Seq Mon Tue Wed Thu Fri Sat Sun
1 5
2 2
3 1 1
That is, those days with the most number of jobs shown first (Monday only shows 5 because of the true/false flag in the sample data - Monday actually has 8 jobs).
But even that doesn't make sense really. If your just trying to show a summary of the week, then why not simply:
Mon Tue Wed Thu Fri Sat Sun
5 1 2 1
unless your just trying to make the highest to lowest stand out. To add a little bit more, here's the count of jobs by date:
and here's the number of jobs by date, showing the first job for that date:
and finally your re-worked crosstab showing what I suggested above:
Note that there is no seq column (1,2,3,4), but the days with the most jobs are shown first, followed by the days with fewer jobs.
Jim.
ASKER
Sorry it's unclear. The output I want is exactly what the query is giving. It's just that the query has the massive overhead of running the DCount function that you rightly pointed out makes it very inefficient. I am looking at another way of representing the data. To make it more meaningful have a look at the attached. The query shows how the customer wants to see the data in principle.
Temp.accdb
Temp.accdb
OK, that was helpful. I can see what the intent is now, but the sequence column doesn't make sense.
In that last DB, your now showing output where for each day, the name and location of each job is being shown.
I'm assuming based on your question that you want that simply to be numbered:
1
2
3
4
5
6
7
8
so that when I look at any given day, I can go down to the last job listed, then run over to the left to know how many jobs I have on that day, correct?
That is not what the current DCount() is giving, which is why I'm confused and with the lack of real data in the first test db, it was hard to figure out what really was being shown.
Jim.
In that last DB, your now showing output where for each day, the name and location of each job is being shown.
I'm assuming based on your question that you want that simply to be numbered:
1
2
3
4
5
6
7
8
so that when I look at any given day, I can go down to the last job listed, then run over to the left to know how many jobs I have on that day, correct?
That is not what the current DCount() is giving, which is why I'm confused and with the lack of real data in the first test db, it was hard to figure out what really was being shown.
Jim.
ASKER
Still not sure that I've explained it. The DCount is simply giving me the ability to group all the first records of each day with the first record of every other day and the second record of each day with the second record (if it exists) of other days. If you can make the query show the same data without the DCount that's fine. I don't need that column, I just want it to show the data as indicated. The reality is that in most cases there won't be more than 3 or 4 jobs per day. Maybe I just don't understand cross tabs but I can't figure out how to display the data as shown without the DCount column
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, thanks. I can see what you've done and I can apply that. That gets me going and improves efficiency. Thanks.
I like your idea of creating a temp table but not quite sure at what point you're suggesting the data go into that table to maximise speed.
Don't worry about time taken, you have no idea how grateful I am to have finally found a solution. I used this approach for another business I worked for and it really did slow things down. I didn't want to repeat the problem here.
I like your idea of creating a temp table but not quite sure at what point you're suggesting the data go into that table to maximise speed.
Don't worry about time taken, you have no idea how grateful I am to have finally found a solution. I used this approach for another business I worked for and it really did slow things down. I didn't want to repeat the problem here.
<<
I like your idea of creating a temp table but not quite sure at what point you're suggesting the data go into that table to maximise speed.
>>
Right where you would assign the "row" or "rank" for the job. If you use a temp table, as your filling it you can call a function to set that value. You'd then base the final cross tab off the temp table.
Using a static or global variable to hold the count from call to call and also the last plan date, you can pass the current plan date in for each row.
If it's un-changed from the last plan date, you'd increment the counter and pass it back. If the plan date changed, then you reset the counter to 0 and save the plan date passed in as the new lastplan date, and fall through the rest, which would increment the count (so you'd end up starting at 1)
This would be far faster than what your doing now, which is using a subquery. With the subquery, your running a separate query for each row. It may be acceptable however because as you said, the number of jobs in any given week is small.
I think as long as plan date is indexed, it will suffice, but I wanted to make sure you were aware that a faster method was available. However it carries its own problems as well, so stick with this if it works fast enough for you.
Jim.
I like your idea of creating a temp table but not quite sure at what point you're suggesting the data go into that table to maximise speed.
>>
Right where you would assign the "row" or "rank" for the job. If you use a temp table, as your filling it you can call a function to set that value. You'd then base the final cross tab off the temp table.
Using a static or global variable to hold the count from call to call and also the last plan date, you can pass the current plan date in for each row.
If it's un-changed from the last plan date, you'd increment the counter and pass it back. If the plan date changed, then you reset the counter to 0 and save the plan date passed in as the new lastplan date, and fall through the rest, which would increment the count (so you'd end up starting at 1)
This would be far faster than what your doing now, which is using a subquery. With the subquery, your running a separate query for each row. It may be acceptable however because as you said, the number of jobs in any given week is small.
I think as long as plan date is indexed, it will suffice, but I wanted to make sure you were aware that a faster method was available. However it carries its own problems as well, so stick with this if it works fast enough for you.
Jim.
ASKER
Thank you so much again Jim. My objective is as much to learn as it is to find a solution and you have certainly helped me achieve both.
Select YourDate, Count(*)
From YourTable
Group By YourDate;
Then join to this count query on date and use that as the source for the crosstab.