Record Count for Dashboard help

[extremeshannon]

Hello all,
I could use a little guidance as to how I can achieve a Count of records on my Dashboard. I have a Status Field and would like to count records in different status and display on Dashboard.

Field Name Status
Inwork
on Hold
Complete

I would like to display the status name with how many are in the status and a total number of records. If you can point me in the right direction I think I could figure it out.

Thanks
Shannon

 

[isladogs]

Use the DCount function https://support.office.com/en-us/article/DCount-Function-F6B5D78B-AD0B-4E42-BE7A-11A64ACBF3D3

 

[extremeshannon]

Thanks for the fast response. I have gotten this to work on a field that is short Text. I get an error when I do this on a Field that is a lookup from another table. The Datatype is Number and is a combo box pointed to a status table.

Thanks Shannon

 

[isladogs]

I can't picture what you mean where it didn't work.

Do you mean a table containing a lookup field? If so, these are a bad idea for many reasons.
What is the expression you used and what error do you get?

 

[extremeshannon]

Lets see if I can explain Correctly. I created a Separate Table Called Status with the Values I would use for status, Complete, Inwork, On Hold. On my equipment table I have a field Called Status and under Data Type i selected lookup and selected that table. now I can select on of the Values from that table and not have to type them in every time.


=DCount("*", "Equipment", "[Status] = 'Complete'")

 

[isladogs]

This results in a combo in the table fields. As I said its a bad idea.
See http://www.theaccessweb.com/lookupfields.htm

You didn't state what error you got. Possibly a type mismatch error

In order to make this work, I think you will need the PK field value from the Status table for the record where Status = Complete e.g. Status =1
This will get very confusing.....AVOID

You also shouldn't have a table Status with a field having the same name

 

[arnelgp]

=dcount("*","equipment","status=" & dlookup("id","statusTableName","statusFieldName='Complete'"))

=dcount("*","equipment","status=" & dlookup("id","statusTableName","statusFieldName='Inwork'"))

=dcount("*","equipment","status=" & dlookup("id","statusTableName","statusFieldName='On Hold'"))

replace "statusTableName" and "statusFieldName" with the table name and field for Status table.
also "id" with the name of your autonumber field in status table.

 

[isladogs]

Arnelgp’s solution should work
However using both DCount and DLookup in each expression is likely to be very slow especially if you have a lot of records to first search and then count.

This perfectly illustrates why lookup fields in tables are a bad idea

 

[extremeshannon]

Thanks everyone I got the Dcount to work. I have removed all Look up fields. Now I am trying to figure out how to get the data on the forms to work like they did before. I will make separate post with my problems on this matter. thanks again

Shannon

 

[theDBguy]

Thanks everyone I got the Dcount to work. I have removed all Look up fields. Now I am trying to figure out how to get the data on the forms to work like they did before. I will make separate post with my problems on this matter. thanks again

Shannon

Hi Shannon. You can use Comboboxes on your forms to perform the same function as the "lookup field" did. This is actually where you want to use a combobox, not in the table.

 

[extremeshannon]

Hi Shannon. You can use Comboboxes on your forms to perform the same function as the "lookup field" did. This is actually where you want to use a combobox, not in the table.

Thanks for the reply. That is what I am trying to do but I get an Error "The value you entered isn't valid for this field" I have a foreign key in the master table and I understand it is a number data type. Could you send me any reference or tutorial on how to do this correctly.

Shannon

 

[theDBguy]

Thanks for the reply. That is what I am trying to do but I get an Error "The value you entered isn't valid for this field" I have a foreign key in the master table and I understand it is a number data type. Could you send me any reference or tutorial on how to do this correctly.

Shannon

Hi. Let's say you have a number field, which is a foreign key to a lookup table. You could add a combobox on the form using the Wizard. As you step through the Wizard prompts, select the lookup table and make sure to include, as the first column, the PK field of the lookup table. When the Wizard asks you what you want to do with the value in the Combobox, select the one that says to store it in your table and select the FK field. Hope it helps...

 

[extremeshannon]

Thanks for the reply looks like the info you provided helped me figure it out.

Thanks again everyone

Shannon

 

[theDBguy]

Thanks for the reply looks like the info you provided helped me figure it out.

Thanks again everyone

Shannon

Hi Shannon. You're welcome. Glad we could all help. Good luck with your project.