Excel VBA Access Query

[mikeymay]

I have an Access Table that holds the following fields with examples of their content

• Name (A Smith)
• Date (3rd Jan 2020)
• Day (Friday)
• Rate (172.50)

I need to return a list in a date range that will show the following

• Name
• Count of weekdays (not Saturday or Sunday)
• Count of Rate where value is > 0.00

The list needs to show by distinct name only and to show the totals rather than a line per record.


Thanks

 

[Trevor G]

Create a Query in the database that uses the fields you want and then add an expression to format the date field to show the days of the week, then set the criteria to exclude Saturday and Sunday. Finally group against the fields you want to count against rather than sum. Example of the expression is this:

Expr1: Format([Date],"dddd")

Criteria for excluding the dates is this:

<>"Saturday" And <>"Sunday"

 

[mikeymay]

Thanks for this Trevor.

I'm building this query in Excel VBA rather than in Access.

I am OK with simple queries when doing it this way but I really struggle with returning unique values and counts, sums, etc.

The format I'm used to is as follows
SELECT Exp_Employee, Exp_Type, Nominal_Code, Item_Description, Purchase_Reason, Net, VAT, Gross FROM Expenses WHERE IsNull(Exp_Pay_Pack) OR Exp_Pay_Pack=

I know I should really write these in Access first to test and then convert to Excel VBA but it's something I never got round to learning properly.......

 

[Trevor G]

If that's your preference then why not get the data in and create a pivot table via VBA to give you the results you want.

 

[Trevor G]

Here is an example of the SQL code behind the query in Access to do grouping and counting. I hope it helps you find a solution.

SELECT tableName.LastName, Count(tableName. Amount) AS CountOfAmount, tableName.Date

FROM tableName

GROUP BY tableName.LastName, tableName. Date

HAVING (((tableName. Date) Between #4/1/2019# And #4/30/2019#));

 

[Trevor G]

This line can be used to not include Saturdays and Sundays.

WHERE (((Format([Date],"dddd"))<>"Saturday" And (Format([Date],"dddd"))<>"Sunday"));

 

[mikeymay]

Thanks Trevor, I'll give it a go.