Solved

Need help to create a query with a Row number

Posted on 2014-09-17
27
244 Views
Last Modified: 2014-09-25
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
Comment
Question by:Rob4077
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 9
  • 5
27 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40328148
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
 
LVL 57
ID: 40328309
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
 
LVL 57
ID: 40328323
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:PatHartman
ID: 40328384
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
 

Author Comment

by:Rob4077
ID: 40329361
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
 

Author Comment

by:Rob4077
ID: 40329363
Sorry, where I said Monday above I meant Wednesday
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 40330389
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
 

Author Comment

by:Rob4077
ID: 40330483
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 40330538
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
 

Author Comment

by:Rob4077
ID: 40330565
But I need all 3 rows.
0
 

Author Comment

by:Rob4077
ID: 40330570
I have to quit for tonight but I will try to create a mini sample database of what I need tomorrow
0
 

Author Comment

by:Rob4077
ID: 40331708
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 40333087
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
 
LVL 57
ID: 40333177
@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
 

Author Comment

by:Rob4077
ID: 40334246
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
 

Author Comment

by:Rob4077
ID: 40339205
Hi, Just wondering if you managed to download the file?
0
 
LVL 57
ID: 40339248
I can download zip and the first fine here....just have gotten side tracked.

I'll look at it now.

Jim.
0
 
LVL 57
ID: 40339935
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
 

Author Comment

by:Rob4077
ID: 40340528
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
 
LVL 57
ID: 40341420
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
 

Author Comment

by:Rob4077
ID: 40341497
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
 
LVL 57
ID: 40341703
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
 

Author Comment

by:Rob4077
ID: 40341825
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40342170
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
 

Author Comment

by:Rob4077
ID: 40342977
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
 
LVL 57
ID: 40343862
<<
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
 

Author Comment

by:Rob4077
ID: 40345322
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question