Group and count functons - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Group and count functons

David Cummins

2 years ago

I built a table and form to record and calculate payments to persons based on the total number of days they worked for each month. They are paid based on the total number of days worked times their daily rate. I created a query for my supervisor which lists all the individuals by each day and the amount they earned each day. The supervisor wants a summary showing total days each person and the total fee due to them. I tried using the TOTALS function to Group and Count but these does not work because the function wants to group all the columns rendering no grouping at all.

My workaround was to export the query as an Excel worksheet where the supervisor manually filters and totals each person.

My question, is there a way to create and apply a VBA script in Access to automate the export to filter and total each person in the Excel file?

	Sami Shamma @Reply 2 years ago
David There is no need to export to excel. This is easily done inside of access. Richard has videos that show you exactly how to do what you're wanting to do. He did it on sales rather than salaries but it is the same. Sales By Weekday

	Sami Shamma @Reply 2 years ago
Dashboards

	David Cummins OP @Reply 2 years ago
Hi Sami, I looked at this but I my query is based on several tables and Group and Total functions cannot be applied using multiple tables. So, Access is not very good for data analysis, which is why Excel is a better tool to perform the functionality I need. I am going to play with Power BI to see if can better automate the filtering and sum functions from the Access Excel export.

	Juan C Rivera @Reply 2 years ago
try this one Time Card it may be a diffrent approach that may work for you... V/r Juan

Adam Schwanz @Reply

2 years ago

Access should still be able to do it, how many tables are involved? Could we get a screenshot of what you're trying to do? I've made some pretty complex queries and always been able to manipulate what I wanted to get. In some rare cases it needed two queries (one to group/filter or do some kind of calculation for the second) but could always be done.

Absolute worst-case scenario, you can make a temp table and use a recordset to create the data that you want inside Access.

David Cummins OP @Reply

2 years ago

Hi Adam,

I am using 5 tables. I have never been able to create a query with three tables, which I guess is the limit. I can't post a screenshot because I am on a Windows 11 machine that no longer supports Snipping tool images.

What is tripping me up is that my StartDate and EndDate fields. When I delete the Group function from the TOTAL in design mode I get the "Your query does no included the specific expression "StartDate" as part of the aggregate function" message.

I will try making a temp table as you suggested but I am not sure of Access can create queries with more than 3 tables.

	Sami Shamma @Reply 2 years ago
1)For Screen shots use Ctrl+ Print Screen then past to paint. 2) I have Queries with 12 tables. 3 is not the limit.

Adam Schwanz @Reply

2 years ago

Yea I've done more then 3, Ive done more than 3 with queries that have multiple tables inside of each of them even in a single query.

Sometimes when you make changes to a query or table, the SQL gets screwed up or a piece gets left in that should be removed. If you go to the query and change the view to SQL view you could usually see the part that is stuck in there and manually delete it (assuming you can read along with SQL).

If you can upload some screen shots of what you have and what you need it to do, I'm sure Access can do it, we can probably help get you there.

Kevin Yip @Reply

2 years ago

Hi David, there is no 3-table limit in queries. A few months ago you asked us about this ( https://599cd.com/blog/display-comment.asp?CommentID=77302 ), and we already said there was no such limit. You need let go such false info before you can begin to find a solution.

You can make screenshots by pressing the "Print Screen" key on your keyboard, then paste the copied image into the Paint program. The Print Screen key has worked pretty much since the beginning of time (it worked even in DOS, I believe).

David Cummins OP @Reply

2 years ago

Hello, gentleman.

I would like to submit a request for a bid to fix my app.

I have six tables and it is not possible to apply the Totals, Grouping and Sum functions using agg queries. I suspect a combination of VBA code and advanced SQL is required that is beyond my current ability to achieve the desired result.

I have a detailed description of the database. It is a PDF file. is there a way to post it here? If not, where and how?

Thanks.

	Richard Rost @Reply 2 years ago
David you can post requests to hire a consultant in the Developer Network Forum. You can't post PDFs but you can post screen shots of them.

	John Davy @Reply 2 years ago
Hi David Have you tried several simple queries instead of 1 complex query John

	David Cummins OP @Reply 2 years ago
Hi John, I tried using simple but could not get the grouping the way I want it to work. I can group the families together with a sum for total days and total fees. The problem is that I have to attach the foster youth with each date transaction with the Youth's case manager staff person. This is where my limited programming skills come into play.

	Kevin Robertson @Reply 2 years ago
Can you post some screenshots of your queries so we can see what you have tried?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View