Need help with Dcount function

[Uvuriel03]

I have a table with the following information on it:

ID Number; Part Department; Truck Number

Each ID has one of 3 Part Departments, E, P, or H. There are several ID numbers associated with each Truck number. Here's a sample of my data:

The pink and purple shaded areas are both from Truck 13. As you can see, the pink lines are all Part Department "H", and the purple ones are Part Department "P".

Now for the fun part. I can't figure out how to make the following to happen...

If ANY ID within a truck number is Part Department H, all of the IDs on the entire truck need to be counted as H.

IE, even though 7 of the IDs on Truck 13 are from Department P, those seven need to be counted as H since there is at least one ID from Department H on that truck. (If there were NO IDs from department H, then each ID would keep its original Department letter.)

I THINK I need to use a Dcount function in a new query to get the result I want, but... I've been struggling with it.

NOTE: I *can* get the result I'm looking for in Excel with the Countifs function. It lets you use 2 criterion, so I can say,

=IF(COUNTIFS({Truck},{Whichever truck this line is},{Department},{"H"})>0,"H",{Whichever Department this line is}).

It's literally =IF(COUNTIFS(E:E,E2003,C:C,"H")>0,"H",C2003).

Now I just need to figure out how to get the same result in a query in Access.

Any help would be greatly appreciated! Thanks!

 

[jal]

Try this:

[SIZE=2]SELECT Truck, COUNT(Truck) as TheCount, TheType[/SIZE]
[SIZE=2]FROM[/SIZE]
[SIZE=2](
[INDENT]SELECT *, "H_Truck" as TheType FROM tblParts
[/SIZE][SIZE=2]WHERE Truck IN [/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2]SELECT DISTINCT Truck FROM tblParts[/SIZE]
[SIZE=2]WHERE Dept LIKE 'H'[/SIZE]
[SIZE=2]) [/SIZE]
[/INDENT][INDENT] 
[SIZE=2]UNION ALL[/SIZE]
 
[SIZE=2]SELECT *, "Not_H" as TheType FROM tblParts[/SIZE]
[SIZE=2]WHERE Truck NOT IN[/SIZE]
[SIZE=2]([/SIZE]
[SIZE=2]SELECT DISTINCT Truck FROM tblParts[/SIZE]
[SIZE=2]WHERE Dept LIKE 'H'[/SIZE]
[SIZE=2]) [/SIZE]
[/INDENT][SIZE=2])[/SIZE]
[SIZE=2]GROUP BY Truck, TheType[/SIZE]

 

[jal]

I'm not sure you ever told us the name of the table. I called it "tblParts"

 

[jal]

That query reports on all trucks. If you need to report on a single truck at a time, just put a WHERE clause before the Group By clause

WHERE Truck = 12
GROUP BY Truck, TheType

or on a form it might be:

WHERE Truck = Forms!myForm!textboxTruckNumber
GROUP BY Truck, TheType

 

[jal]

Also, I forgot to mention, if you really want to use DCount (well in this case you can use DLookup to get the count) then store it as a query object, say qryGetCount. Then if you want to display in a textbox the count for truck 12:

txtBox1.Value = DLOOKUP("TheCount", qryGetCount, "Truck = 12")

 

[Uvuriel03]

Hey, thanks for your response.

I'm having a little bit of trouble using your code, though. I don't have anything called TheType in my tables... Is this a column I need to create or something? What is this supposed to be referencing?

And also, is this code something I need to paste into a query's SQL?

Thanks!

 

[jal]

TheType column is added on the fly (i.e. created by the query) for documenting purposes (for display purposes basically). You can remove it from the query if you like, although it helped me get my bearings during design.

Anyway create a new query object, paste the code into SQL view of the query, and save it as, maybe,

qryCountIDs



Then you can use it on a form, for example, like this

txtBox1.Value = DLookup("TheCount", "qryCountIDs", "Truck = 12")

or

txtBox1.Value = DLookup("TheCount", "qryCountIDs", "Truck = " & txtTruck.Value)


or something like that. I was hoping you already knew some methods of putting the query to use - I was just trying to get you started writing the query.

 

[Uvuriel03]

Yes, I do know how to use queries--I just didn't recognize the way you coded yours. It was a little different than what I'm used to seeing. I think I got it all figured out now though!