Solved

Need help to create a query with a Row number

Posted on 2014-09-17
27
235 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
  • 13
  • 9
  • 5
27 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, where I said Monday above I meant Wednesday
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
But I need all 3 rows.
0
 

Author Comment

by:Rob4077
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
@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
Comment Utility
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
Comment Utility
Hi, Just wondering if you managed to download the file?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
I can download zip and the first fine here....just have gotten side tracked.

I'll look at it now.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now