Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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","tblJobDetails","[DatePlanned] is null AND IncludeInWeekView = -1 AND [DateRequested] = " & CLng([DateRequested])) AS DisplaySeq,
"##" AS SupplierIde, "TBA" AS Supplier, tblJobs.Jobnum, tblJobDetails.daterequested, Abbrev([LinkedCustomer],[tblJobDetails].[Activity],[JobName],[Supervisor],[DetailId],[fkActivityID],[fkJobNum]) AS Details, tblActivities.IncludeInWeekView, tblJobDetails.DateRequested, tblJobDetails.DatePlanned
FROM (tblJobs LEFT JOIN tblSupervisors ON tblJobs.fkSupervisorId = tblSupervisors.id) RIGHT JOIN (tblSuppliers RIGHT JOIN (tblJobDetails LEFT JOIN tblActivities ON tblJobDetails.fkActivityId = tblActivities.Id) ON tblSuppliers.SupplierId = tblJobDetails.fkSupplierId) ON tblJobs.Jobnum = tblJobDetails.fkJobNum
WHERE (((tblActivities.IncludeInWeekView)=True) AND ((tblJobDetails.DateRequested) Between Date() And Date()+6) AND ((tblJobDetails.DatePlanned) Is Null))
ORDER BY tblJobDetails.DateRequested;
0
Rob4077
Asked:
Rob4077
  • 13
  • 9
  • 5
1 Solution
 
PatHartmanCommented:
Create a separate query that counts by day.

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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
PatHartmanCommented:
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.
0
 
Rob4077Author Commented:
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
0
 
Rob4077Author Commented:
Sorry, where I said Monday above I meant Wednesday
0
 
PatHartmanCommented:
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 

Open in new window

0
 
Rob4077Author Commented:
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.
0
 
PatHartmanCommented:
The THREE "3" rows in the base query will end up as ONE "3" row when you run the cross tab.  Just try it.
0
 
Rob4077Author Commented:
But I need all 3 rows.
0
 
Rob4077Author Commented:
I have to quit for tonight but I will try to create a mini sample database of what I need tomorrow
0
 
Rob4077Author Commented:
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
0
 
PatHartmanCommented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
@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.
0
 
Rob4077Author Commented:
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
0
 
Rob4077Author Commented:
Hi, Just wondering if you managed to download the file?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I can download zip and the first fine here....just have gotten side tracked.

I'll look at it now.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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:

screen shot
What's the end output your looking for?

Jim.
0
 
Rob4077Author Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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:

ss1
and here's the number of jobs by date, showing the first job for that date:

ss2
and finally your re-worked crosstab showing what I suggested above:

ss3
  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.
0
 
Rob4077Author Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.
0
 
Rob4077Author Commented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
OK, here you go.    Performance should be better, but what your looking to do still requires a sub query if you stick to strictly SQL.

A faster approach would be to create a temp table and when executing an append (INSERT) query to fill the table, you can simply call a VBA function, which would be very fast. Then you'd base the cross tab off the temp table.  

That would be the way I'd go I think, but give this a whirl and see if it works fast enough for you.

 Make sure the table is indexed on PlanDate on table 1.

Jim.

PS. Really sorry it took so long to get this answered for you.
Temp.accdb
0
 
Rob4077Author Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<
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.
0
 
Rob4077Author Commented:
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 13
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now