count in access

[spinev]

Hello,

I've been searching all evening on how to use a sumproductif function, like the one in excel in access. I have a table containing the calendar dates for ten ten years.

so i have column 1 - date, column 2 i extracted the year, column 2 the month and column 3 the mondays. so it would look as follows:

date | year | Month | Mondays

i need to calculate total number of mondays of every month of each year, so i would have a value of the particular year with the particular month and the number of mondays in that month.

i tried to create a new table with field Year|month|mondays, so when i choose the year and the moth i get a result of the count of mondays as described above. In excel i have this formula but I cannot figure out how to do it in access -| =COUNTIFS(Calendar!$D:$D,'Employees 2023'!$E$8,Calendar!$B:$B,'Employees 2023'!C$9,Calendar!$E:$E,"Monday") |

can you guide me to a solution? i can easily do it in excel and then copy the results, but I would like to do it automatically with access.

thank you in advance

 

[plog]

First, you shouldn't have a field called 'date'. It's a reserved word and makes coding/querying more difficult. I suggest giving it a prefix or suffix for what it represents (e.g. SalesDate, AttendanceDate, DateAcquired). Second, you probably don't need that table at all. You should most likely just build a function to provide the ultimate information you need. Third, you definitely don't need the last 3 fields of that table (year, Month, Mondays) all of those fields are calculable with just a date and you don't store calculated data.

I have a table containing the calendar dates for ten ten years.the number of mondays in that month.

Again, that can be obtained via a function--you pass the function a date (or month/year) and it uses logic to return the number of mondays in that month (4 + 1 if the month starts on certain days). However, you can also do this in a query with just the date field (which you should rename) of your table using the Weekday function (https://www.techonthenet.com/access/functions/date/weekday.php), you can do this with a query:

SELECT Month([date]) As ReportMonth, Year(date) As ReportYear, COUNT([date]) AS Mondays
FROM YourTableNameHere
WHERE Weekday([date])=2
GROUP BY Month([date]), Year([date]);

Again, again--the method you are persuing is probably not the best method to achieve what you want to ultimately achieve--whatever that may be.

 

[Pat Hartman]

1. What Exactly are you going to do with this table?
2. Are you trying to make this table based on historical records or are you trying to project future data?

 

[arnelgp]

use Aggregate (Total) Query:

SELECT
    Format$([YourdateFieldName],"yyyy-mmm") AS YrMo,
    Sum(IIf(Weekday([YourdateFieldName])=2,1,Null)) AS TotalMon
FROM yourTableName
GROUP BY Format$([YourdateFieldName],"yyyy-mmm");

 

[spinev]

Hi All, thank you for your replies. Will try it out later and let you now how if i manage to do it.

I did this calendar as i included the holidays of the coming 10 years in it.....but at this point seeing that i can use the date anyway i think i could simply used the holiday dates in a table and look for them through the date.....am i correct?

Basically i need the data to calculate the result with a formula in a form. As you might seen in the excel formula, i am building a payroll/attendance database instead of using excel. I need this data to calculate tax which is based on mondays per year. For example the tax is 2 each monday, january has 5 mondays, so total is 10: February has 4 and total is 8, etc... it would have been easier to have it manualy inputed, but i do not like the easiest way out if there is a way to do it automatically .

@arnelgp is this sql or vba?

 

[arnelgp]

it is an SQL, goto sql designer and in SQL view paste the code.
then replace [YourdateFieldName] with your correct date field and
yourTableName with your table name.

 

[arnelgp]

so if you are multiplying it with 2, change the query to:

SELECT
    Format$([YourdateFieldName],"yyyy-mmm") AS YrMo,
    Sum(IIf(Weekday([YourdateFieldName])=2,1,Null)) * 2 AS TotalMon
FROM yourTableName
GROUP BY Format$([YourdateFieldName],"yyyy-mmm");

 

[spinev]

Hi All. Sorry for the late response but was a bit busy on work.

both version work like a charm, the total Mondays per month are correct.

@arnelgp: as mentioned I need the total mondays per month as the tax is worked each monday.....so yes it is easy to make the total monday multiply by 2, however taxes change on a yearly basis and I need to this with a formula. Firstly cause the 2 was just a figure as an example, and secondly the mutiplying factor is layerd.....I already have this covered though in my form, however I was inputting the mondays manually in a field which is part of the whole formula to get the result needed.

Now my other part is how to get this monday totals each month in the form.
so my form has a month drop down field and a year field which is manually inserted and a mondays field. Currently the mondays field is manually inserted. No I just need to have the calculated mondays from the query and copy them in the field.......the tricky part is that these should tally with the year and the month I choose in the form. Basically i was thinking to use the IF, And , Then functions. any suggestions? shal I put the code in VBA with the other codes and formulas?

 

[arnelgp]

see this demo. Open Form1 and select month/year from the combobox.
view the form in design view and see the Code in the combos AfterUpdate event.
press Ctrl-G, and see the code of the function.

 

[spinev]

@arnelgp

thanks a lot for the sample, was very instructive.....worked like a charm in my project.

 

[spinev]

Hi again,

I mainly finished the table and forms in my project and seems all is working...so far

i am now compiling the reports. and have a question........i am creating a report with the data in my tables and queries, so far all is set, however my question is, I need to insert a text box where, when I load the report a pop up comes up and asks t insert the dates from and date to.....which I would like to do manually. Is this possible to have a text box just with data for the report....I do not need to store these dates......so in other words when I run up the report, before it loads, the popup comes up and i insert the date from and date to in 2 separate fields which then are shown on the same report for printing or convert to pdf.

 

[Gasman]

Use a form. Then you can validate input.

 

[spinev]

Use a form. Then you can validate input.

Can i use a vba pop up in the report to inset the dates i need? As I do not want to record these dates? This is going to be used just for the report generated.

 

[arnelgp]

you can use Inputbox and Tempvars to gather your 2 dates.
see the Open event of sampleReport report.

 

[Gasman]

Can i use a vba pop up in the report to inset the dates i need? As I do not want to record these dates? This is going to be used just for the report generated.

Yes, then I can put in any old date like 30/02/2024

 

[spinev]

you can use Inputbox and Tempvars to gather your 2 dates.
see the Open event of sampleReport report.

Once again thank you arnelgp. The tempvars worked fine. I have finally made the bd workable....still need a lot of fine tunining but for now it doing its job