Database Summary (1 Viewer)

dbuckmas

New member
Local time
Today, 04:20
Joined
Jan 18, 2018
Messages
1
Hi!

I have created a large database with several thousand records. These records contain the following:
  • Unique ID
  • Department (short text)
  • Priority (number 1,2,3)
  • Completion (limited text “complete”/“incomplete”)
I would like to create a form that displays the following metrics for each combination:
  • Total records
  • Completed records
  • % records complete

Could anyone recommend any methods or resources fee to help me? I began with a cross tab query but I am having difficulty with showing the combinations that are valued at null, and linking the query results to a form.

Thanks!
Dan
 

plog

Banishment Pending
Local time
Today, 03:20
Joined
May 11, 2011
Messages
11,613
I really think a Report would be a better end product for this than a report. Actually, given that you have started down the cross-tab query, you might be best served shooting the aggregate data to Excel and creating a Pivot table.

As for the query itself, I suggest a simple Aggregate query: https://support.office.com/en-us/ar...-a-query-b84cdfd8-07ba-49a7-b067-e1024ccfcca8

Don't cross-tab it, get the data you want to appear in that query first, then move on to making it display like you want.

am having difficulty with showing the combinations that are valued at null,

If your underlying data source in your query does not contain the combinations you want to report on, then you need a datasource that has those combinations. Do you have a list of all Departments? Do you have a list of all priorities?
 

Mark_

Longboard on the internet
Local time
Today, 01:20
Joined
Sep 12, 2017
Messages
2,111
If you do a search for "access vba dcount" you should be well on your way.

DCount can let you know how many meet each criteria. From there it is simple calculations.
 

isladogs

MVP / VIP
Local time
Today, 08:20
Joined
Jan 14, 2017
Messages
18,186
Dbuckmas
This is almost identical to thread you posted earlier today at
http://www.accessforums.net/showthread.php?t=70097 and which I helped you get a solution based on an aggregate query and DCount

You then said you solved it using a crosstab for some reason

All of this was before you posted this thread here. Why have you done so?
And if you do cross post, say you have done so and provide the link

You are getting exactly the same advice here...because it's the best solution
 

Users who are viewing this thread

Top Bottom