Unique Count in Query

[CDF]

Hi,

I'm having trouble working out the following.

I've got 2 tables (tbl_Customer & tbl_Vehicle) linked by a Customer_ID
field with a 1:Many relationship (1 customer (Customer_ID (PK)) can
have many vehicles (Customer_ID (Fk))).

I have the following query which show a count of my Vehicle_ID and
Customer_ID

SELECT tbl_Vehicle.Vehicle_ID, tbl_Vehicle.Customer_ID, Count
(tbl_Vehicle.Vehicle_ID) AS CountOfVehicle_ID, Count
(tbl_Vehicle.Customer_ID) AS CountOfCustomer_ID
FROM tbl_Customers INNER JOIN tbl_Vehicle ON tbl_Customers.Customer_ID
= tbl_Vehicle.Customer_ID
GROUP BY tbl_Vehicle.Vehicle_ID, tbl_Vehicle.Customer_ID;

It spits out the following:

Vehicle_ID Customer_ID CountOfVehicle_ID CountOfCustomer_ID
17 1 1 1
23 1 1 1
24 6 1 1
30 6 1 1
34 8 1 1
36 8 1 1
46 8 1 1
48 8 1 1
49 8 1 1

As you can see I would like it to count 9 vehicles and 3 Customers.
I'm sure I'm going about it the wrong way!!

The reason for needing this is that I have a startup Form acting as a
Main Menu with a Customer Tab and a Vehicles Tab. On the customer tab
I'd like to show "x customers in database" on the Vehicle tab I'd like
to show "x vehicles in database".

As the form can only run from one query, I can't add my two single
table queries of:

Customer:
SELECT tbl_Customers.Customer_ID, Count(tbl_Customers.Customer_ID) AS
CountOfCustomer_ID
FROM tbl_Customers
GROUP BY tbl_Customers.Customer_ID;

and

Vehicle:
SELECT tbl_Vehicle.Vehicle_ID, Count(tbl_Vehicle.Vehicle_ID) AS
CountOfVehicle_ID
FROM tbl_Vehicle
GROUP BY tbl_Vehicle.Vehicle_ID;

Am I meant to do some sort of subquery or Select Distinct query in my
initial statement above or am I supposed to do something completely
different??

Thanks for any help.

 

[Neil]

If you want a count of customers, you have to do that in its own query. A
count of vehicles needs to be in its own query as well.

You say the form "can only run from one query." But why is your form bound
to a query, anyway, if all you want is to show a single value? That's not
necessary at all. You bind a form to a query when you want to access
multiple records.

There are two approaches here to resolve your problem.

In the Form Open event, open a recordset based on the count of customers,
get that value, then do the same for count of vehicles, and assign those
values to locked text boxes on your form. Something like:

Dim rs as recordset
Dim strSQL as string

strSQL = "Select Count(Customer_ID) As CustCount From tbl_Customers"
set rs = currentdb.openrecordset(strsql, dbopensnapshot, dbfailonerror)
if not rs.eof then me!txtCustCount = rs!CustCount

' now do the same for vehicles.

The other way would be to simply use DCount in the text boxes' control
source. You'd have two locked text boxes. In the one to show the count of
customers, you'd put in the ControlSource property:

=DCount("Customer_ID", "tbl_Customers")

Then do the same for the text box for count of vehicles.

This latter solution might be the best way to go for your simple needs here.

Neil

 

[CDF]

=DCount("Customer_ID", "tbl_Customers")

Wow! Thanks Neil!

I can't believe how simple it was. I knew I was going about it the
wrong way.

I works perfectly.

Thanks again.

 

[Neil]

Cool. ;-)

Wow! Thanks Neil!

I can't believe how simple it was. I knew I was going about it the
wrong way.

I works perfectly.

Thanks again.

 

[Douglas J. Steele]

Wow! Thanks Neil!

I can't believe how simple it was. I knew I was going about it the
wrong way.

While it shouldn't be an issue in this case (I'm assuming that every record
in tbl_Customers has a value for its Customer_ID field), be aware that when
using a field name for the first parameter of the DCount aggregate function,
rows for which that field is Null are ignored. You're usually best off using
=DCount("*", "tbl_Customers")

 

[Neil]

While it shouldn't be an issue in this case (I'm assuming that every
record in tbl_Customers has a value for its Customer_ID field), be aware
that when using a field name for the first parameter of the DCount
aggregate function, rows for which that field is Null are ignored. You're
usually best off using =DCount("*", "tbl_Customers")

Since he mentioned that Customer_ID was the PK, that won't be an issue. Same
for the field that he's using for vehicle counting. But, yes, using DCount
with "*" is good for just getting the rows in the table. Would work just as
well here.

 

[CDF]

Hi Guys,

Yes I'm using the PK for both so it's not an issue, but I definately
keep it in mind next time I use this function.

Thank you both for your help.

 

[CDF]

Just one more question.

I just realised that the startup form doesn't refresh when I exit the
other forms and come back to it (if I've added a customer and/or
vehicle to the database).

I tried putting a refresh command in the other forms close button but
I get the error 'The command action 'Refresh' isn't available now".

How can I automatically refresh the form when I come back to it?

 

[Neil]

You want to use Requery, not Refresh. When you come back to the form, run:

me.txtcustcount.requery
me.txtvehiclecount.requery

Neil

 

[CDF]

You want to use Requery, not Refresh. When you come back to the form, run:

me.txtcustcount.requery
me.txtvehiclecount.requery

Neil, pardon my lack of access programming knowledge, but where would
I put this code? In the startup forms OnFocus event property? or in
the Customer Forms (and vehicle forms) 'close' button onclick
property?

Also, do I need to change 'custcount' to Customer_IDcount as I don't
use cust anywhere? Or is this the name of the text box showing the
count value??? at the moment they're called Text20 & Text24 as I
haven't given them names???? I assume I should call them custcount and
vehiclecount or txtcustcount and txtvehiclecount???

Thanks for your help.

 

[Neil]

You want to use Requery, not Refresh. When you come back to the form, run:

me.txtcustcount.requery
me.txtvehiclecount.requery

"Neil, pardon my lack of access programming knowledge, but where would
I put this code? In the startup forms OnFocus event property? or in
the Customer Forms (and vehicle forms) 'close' button onclick
property?"

Well, that's a judgment call on your part, depending on how your application
works, and depending on how tightly controlled things are. For example, if
you have your startup form visible all the time, but just have the customer
input form on top of it, then the user might use Ctrl+F6 or the Window menu
to move to the startup menu, and the counts wouldn't be updated if they're
in the Close button click. But if you put them in the form's Got Focus
event, then that might slow things down with recalculations if you go back
and forth to this form a lot wihtout the need for calculation.

So it depends on how your application is set up, and how the users use it.

If you make your startup form not visible when the a form is opened, then
you can just do the recalculation right before you make it visible again.
That would be a great place to do it.

Or, if it remains visible, then you can one of the two opens you noted. If
it were me, and I had to choose one of those two, I'd do it when the
customer form is closed -- but use the On Close event, rather than the Close
button On Click property -- that ensures you cover it regardless of how they
close the form.

Another option would be just to update it when you actually add the record.
The user is expecting a delay there anyway; so another slight delay to get
the count wouldn't be that big of a deal.

So there are some ideas for you. Again, just depends on how your app is set
up and how your users use it.


"Also, do I need to change 'custcount' to Customer_IDcount as I don't
use cust anywhere? Or is this the name of the text box showing the
count value???"

Yes, that's just the name of the text box. I just made up a sample name for
you.

"at the moment they're called Text20 & Text24 as I
haven't given them names????"

A very bad idea. Makes it very hard to maintain your system if you have to
go through code and work with something like Text20_AfterUpdate. Always name
your controls. I do that first thing when I create them (can always change
the name later).

When a control is a bound control, I just leave the name as the name of the
field it's bound to, which is what Access does when it creates the control.
When it's an unbound control (as here), I use the prefix "txt," "cbo,"
"lst," etc., to designate the type of control. This way, not only does it
tell me what kind of control it is, but it also shows me that it's an
unbound control. That's my system, anyway.

" I assume I should call them custcount and
vehiclecount or txtcustcount and txtvehiclecount???"

You can call them whatever you want, whatever helps you to identify the
controls. I would use mixed case (I wrote the original in all lowercase
quickly). Something like txtCustCount or txtCustomerCount would be good. But
you can use whatever you'd like.

HTH,

Neil

 

[CDF]

Thanks Neil,

I coudn't get this to work in any event I tried so I ended up creating
a 'click to refresh' next to the control and putting the code in the
onclick property.

It works fine like this.

Thank you again.

 

[CDF]

a 'click to refresh'

should say 'click to refresh' button

 

[Neil]

Thanks Neil,

I coudn't get this to work in any event I tried so I ended up creating
a 'click to refresh' next to the control and putting the code in the
onclick property.

It works fine like this.

Thank you again.

Great. I'm glad you got it to work. And, since it works in the Click to
Refresh button, that means the code, itself, works. So it's just not getting
triggered through the other method.

Can you tell me what mechanism you set up that didn't work? I'm curious.
Would be better if you can get it working, and keep the user from having to
click the button.

Neil

 

[CDF]

Can you tell me what mechanism you set up that didn't work? I'm curious.
Would be better if you can get it working, and keep the user from having to
click the button.

Neil

Hi Neil,

The startup form (opened on startup via autoexec macro) is always open
in the background acting as a main menu form. It never closes. All
other forms pop up as modal forms in front of it.

If I put the code
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
in the startup forms OnGotFocus event it does nothing.

I don't know what other even on the form to put it in???

If I put it in another forms' 'close' button OnClose event it says

Compile error:
Method or data member not found.

and the <.txtCustomerCount> portion of Me.txtCustomerCount.Requery is
highlighted in the code.

The code for the button is
Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

Dim r As Long
r = MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning")
If r = vbYes Then
DoCmd.Close
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
Else
If r = vbNo Then
End If
End If

But as I said, I've created a 'click to refresh' button on the actual
startup form and it works.

Code is
Private Sub RefreshCustomerCount_Click()
On Error GoTo Err_RefreshCustomerCount_Click

Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery

Exit_RefreshCustomerCount_Click:
Exit Sub

Err_RefreshCustomerCount_Click:
MsgBox Err.Description
Resume Exit_RefreshCustomerCount_Click

End Sub

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Thanks for your help.

 

[Neil]

"If I put the code
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
in the startup forms OnGotFocus event it does nothing.
I don't know what other even on the form to put it in???"

Try it in the OnActivate event. That should work.


"If I put it in another forms' 'close' button OnClose event it says
Compile error:
Method or data member not found."

Yeah, well, the "Me" keyword refers to the form that contains the code
module it's used in. Thus, you can use "Me" in your startup form's code
module, because you're referring to the startup form; but you can't use it
in another form to refer to the startup form.

To refer to another form, you'd use the syntax: Forms!FormName in place of
"Me" (where "FormName" is the actual name of the form).

If I had to call it from another place, I would place the code in a global
module's routine, have that routine run the code to requery the text boxes,
and then call that routine where it's needed. But you should be able to do
it from the Activate event anyway.



Dim r As Long
r = MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning")
If r = vbYes Then
DoCmd.Close
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
Else
If r = vbNo Then
End If
End If

You can clean that code up a bit.

First, you can use ElseIf instead of a nested If/Then:

Dim r As Long
r = MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning")
If r = vbYes Then
DoCmd.Close
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
ElseIf r = vbNo Then
End If

Second, since there are only two options (Yes or No), you don't have to test
for No. If it's not Yes, it will be no. So it can be simplified further to:

Dim r As Long
r = MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning")
If r = vbYes Then
DoCmd.Close
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
Else
End If

But since you're not doing anything if the user clicks No, there's no reason
to test for it in the first place. Thus, you can just have:

Dim r As Long
r = MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning")
If r = vbYes Then
DoCmd.Close
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
End If

Next, we can clean this up further by not storing the return value of
MsgBox. You're only going to use it once, in the If statement; so no reason
to store it. You can just retrieve it in the actual If statement.

Thus, instead of:

r = MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning")
If r = vbYes Then

you can just do:

If MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning") = vbYes Then

So the code would now be:

If MsgBox("Are you sure you want to close the form?", vbExclamation +
vbYesNo + vbDefaultButton2, "Warning") = vbYes Then
DoCmd.Close
Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery
End If

Of course, the Me.txt.... statements don't work (for reasons mentioned
above). I was just showing you how to clean up the code a bit. The cleaner
your code is, the easier it will be to maintain. And it also leaves less
room for errors.

HTH!

Neil





But as I said, I've created a 'click to refresh' button on the actual
startup form and it works.

Code is
Private Sub RefreshCustomerCount_Click()
On Error GoTo Err_RefreshCustomerCount_Click

Me.txtCustomerCount.Requery
Me.txtVehicleCount.Requery

Exit_RefreshCustomerCount_Click:
Exit Sub

Err_RefreshCustomerCount_Click:
MsgBox Err.Description
Resume Exit_RefreshCustomerCount_Click

End Sub

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Thanks for your help.

 

[CDF]

Thank you Neil.

It wouldn't work in the onActivate event so I had to put it in the
other forms' close buttons as Forms!FormName as you suggested.

It now works perfectly, no need for a 'click to refresh' button!!

The cleaned up code works perfectly too.

Again thank you!

 

[Neil]

Thank you Neil.

It wouldn't work in the onActivate event so I had to put it in the
other forms' close buttons as Forms!FormName as you suggested.

It now works perfectly, no need for a 'click to refresh' button!!

The cleaned up code works perfectly too.

Again thank you!

Sure. I hope you learned a few things in the process that will help you next
time as well.

Neil