Solved How to filtering by List box as like cascading combo box (1 Viewer)

[smtazulislam]

Hello Sir @arnelgp
Now facing another problem.

you need to look at the index (Unique) of table tblPayRollData on design view.
this index will prevent duplicate when adding record of same employee, month, and year.

Now Month table make UNIQUE :

SELECT tblMonths.ID
FROM tblMonths
WHERE (((tblMonths.ID)=[Forms]![frmCreateAppend]![cboMonths]));

Combine result is like October : "202210"

Year table

SELECT tblYears.Year
FROM tblYears
WHERE (((tblYears.Year)=[Forms]![frmCreateAppend]![cboYears]));

October Data has been updated !
When try to November data then give message that No record found !
But tblPayRollData is nothing November 2022 data.

 

[smtazulislam]

Hello Mr. @arnelgp . Hope, You are doing well
I having a problem with the data that Its always appended previous 2 months ago records.
In January I thought it was my mistake. February I do it carefully.
see picture.

my questions, why everytime 2 months previous records have been taken ?

 

[arnelgp]

you upload your db so i can troubleshoot.

 

[smtazulislam]

Please check the post #18
My Ideas is MAX formula can work. But no sense which column is perfect to work.
I would like to taken latest records from table "tblPayRollData" .

 

[Pat Hartman]

Without seeing the table schema and the data, we can't tell what is causing the error message. Looks like you don't have the indexes defined correctly.

PS - you REALLY do not want columns named Year and Month. Both are function names and using them can result in strange errors in queries and code.

 

[smtazulislam]

Without seeing the table schema and the data, we can't tell what is causing the error message. Looks like you don't have the indexes defined correctly.

PS - you REALLY do not want columns named Year and Month. Both are function names and using them can result in strange errors in queries and code.

Thanks for you reply.

You can see the table and data in the post #18,
I want to know, where do i mistake that appended query take first rows data only, Not taken latest updated data from the table "tblPayrolldata".

 

[arnelgp]

post #18 db, does not have "Employee Salary Editor" form.

about the append query, it has nothing to do with with any "calculation" you have.
it only append records from Employee records.

 

[smtazulislam]

post #18 db, does not have "Employee Salary Editor" form.

about the append query, it has nothing to do with with any "calculation" you have.
it only append records from Employee records.

Thanks for your response.

I show the employee record editor form for understood records appended FIRST rows only.

Post # 18 attached database is worked perfectly. But problem is the taken records FIRST row only where I would like to taken latest update records each time appended.

For that I tried a subquery as using the MAX formula . Like ...

SELECT tblPayRollData.EmployeeID, Last(tblPayRollData.YearID) AS LastOfYearID, Last(tblPayRollData.MonthID) AS LastOfMonthID, Max(tblPayRollData.PayRollDate) AS MaxPayRollDate
FROM tblPayRollData
WHERE (((tblPayRollData.PayRollDate)=(SELECT MAX(PayRollDate)
             FROM tblPayRollData AS t
             WHERE t.EmployeeID = tblPayRollData.EmployeeID
               AND t.YearID = tblPayRollData.YearID
               AND t.MonthID = tblPayRollData.MonthID)))
GROUP BY tblPayRollData.EmployeeID;

But no sense how to connect with in the append query. because there are already same fields available.

 

[SHANEMAC51]

I show the employee record editor form

• I didn't find where the PAGE_NO list is
• used in three lists, multiple choice is applied - so far I ignored
• the first three lists and the year and month I form a filter, which is called by the FILTER button
• left the button - reset the filter

 

[smtazulislam]

• I didn't find where the PAGE_NO list is
• used in three lists, multiple choice is applied - so far I ignored
• the first three lists and the year and month I form a filter, which is called by the FILTER button
• left the button - reset the filter

Thanks for your response.
No, I dont need filter, it is already done.

I required, APPENDED query.
When i run append query then it is taken Firstly recorded Year and Month DATA what is I disliked.

Thinking an example :
Present data is already there DEC/2022 & JAN/2023 in table "tblPayrollData"

Now I working on FEB/2023 in table "tblPayrollData".
Next Month I work on MAR/2023. If I trying to run append query, It is should take data records from FEB/2023. Because FEB/2023 is my latest update data in table "tblPayrollData".

But unfortunately, its taken data from DEC/2022.
Hope you understand...

 

[arnelgp]

can you post your "tblPayrollData" table?

 

[smtazulislam]

Sorry for late response. Ramadhan Kareem to all.
I uploaded db with "tblPayrollData" table.

I need latest records is taken next appending. Now there have 33 records available. When I tried to appending NEW record that is taken FIRST 33 records. Not token latest records.

 

[arnelgp]

i worked on frmCreateAppend form.
i made 3 queries (qryLatestPayMonthYear, qryDataToAppend and qryAppendData)
you test it now.