Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 7th, 2005, 04:04 AM
Registered User
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Listing records based on a count


I am trying to write a query in Access that will list all the records a table Customers, have more than three records in the table Workorders with a JobTypeId = 2 and JobStatus = 1.

To further complicate it, I also need to take into account that these three plus records must have a DateCompleted greater than the last occurrence of JobTypeID = 2 and JobStatus = 0).

The final twist that has been asked for this morning is the report should also show any records with JobTypeID = 2 and JobStatus = NULL and Date Required in the future.

So, as a newbie to Access how should I tackle this? I'm guessing I'll have to approach it using UNION statements to get the results in one report? Or is Access able to cope with this quite easily?



Old April 7th, 2005, 09:46 AM
Registered User
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts

SELECT Customers.CustomerID, Count([JobStatus]="1" And [JobTypeID]=2) AS KountNA
FROM Workorders AS W INNER JOIN Customers ON W.CustomerID = Customers.CustomerID
GROUP BY Customers.CustomerID
HAVING (((Count([JobStatus]="1" And [JobTypeID]=2))>=3));

Can ayone tell me why the above query would ignore the count criteria and simply return the number of rows inthe Workorders table?

Can you only do things like Count([JobStatus]} to count the number of rows that have a value against them for Job Status?

Old April 7th, 2005, 09:53 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

Actually, this is a little complicated. But once you've done this, it turns out that it's quite easy. You'll build subqueries that you'll join in the final query for your report.

I may not put this together right because I don't fully understand the need. But with the way I'm interpretting it, try this...

Create a query that gives you a list of all work orders that meets your second situation (excluding the bit about the customers having 3 plus). In this case you're looking for JobTypeID=2 and JobStatus=0 and Max(DateComplete). Use the Group By option to group by CustomerID, WorkOrderID and get the Max of DateComplete. This now gives you the customer IDs, workorder ids and the maximum completed date. (Actually, if work order ID is unique independent of Customer ID, you don't need Customer ID in this query.)

Create a query that joins the above query to your WorkOrder table by workorder ID (if workorder IDs are not unique across customers, you need to join customer ID too). Set your criteria to JobTypeID=2 and JobStatus=1 and DateComplete greater than the max completed date from the above query. Use the Group By to get only unique customer IDs and count WorkOrderID (when you count a field, always count one that will never be null because if you Count a field that might be null, the record is not counted). This query now gives you the needed count of Workorders that match JobTypeID=2 and JobStatus=1 and where datacompleted is > than the max for JobTypeID=2 and JobStatus=0 for each customer ID.

It's not clear whether you only want to show only work orders that match the criteria or whether you will show just any customer that matches the situation. I'll take on the showing the work orders part since it's a bit more complicated.

At this point you can create another query that includes your Customers, WorkOrders and the query that counts and the query that gives the Max(datecompleted).

LEFT JOIN the Customers to Workorders by Customer ID. LEFT JOIN the Customers to the counting query by Customer ID, and LEFT JOIN Workorders to the Max(datecompleted) query by WorkorderID. (These joins should have an arrow leading away from the first table to second table or query.)

Based on your post, the first two conditions must be met Simultaneously while the last condition is independent of the first two.

Use two rows to set your criteria, this creates an OR situation.

On one row, check that the count of WorkOrderID from your count query is greater then 3 and that the date of the workorder is greater than the max date from the Max(DateComplete) query. This will show each work order for your first two conditions.

On another row, check that JobTypeID = 2 and JobStatus is null and DateRequired > Date(). This will show each work order for your last condition.

So the whole trick I applied here was to breakdown each component of the request into a query that I used as a subquery. Notice that I threw out the "three plus records and DateCompleted" requirement and decided which information I needed first. I.e. instead of trying to count first, I decided I needed the Max date first. Then I could count those that had a date greater than the max date.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
Old April 8th, 2005, 03:15 AM
Registered User
Join Date: Aug 2003
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks Randall, that clears things up a lot. I didn't realise that Access used COUNT in the same way that Excel did. The old database program that I used allowed you to set conditions when counting.

I'll have a look at putting the query together today.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Count nodes based of if condition suri_1811 XSLT 1 December 7th, 2006 08:00 PM
Pls Help me count records [soon after connection] amjad_mahmood VB Databases Basics 1 August 30th, 2005 07:20 PM
Count of form records siptah Access VBA 3 April 20th, 2005 09:09 AM
Count, sum, count a value, return records CongoGrey Access 1 April 18th, 2005 02:25 PM
Listing records Adam H-W Classic ASP Basics 4 November 17th, 2003 12:15 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.