join query on same table

[attman]

how can i do the query like in the picture

[June7]

What is the logic? Why 30,00 with 1,50 and Feb in both months?

What is your decision process when you analyze this data and derive this output?

[attman]

I need to prepare data like this every month, not just once every 2 months.
according to tax law

[June7]

That doesn't answer question. I don't know tax law. You need to explain the criteria/logic/calculation.

[attman]

I have to declare Simith's a tax this month and b tax next month. tax to be declared in this case: Smith A_pay 30; B_pay 1.5"
In March, I have to pay and report the taxes in February belonging to Simith.

[attman]

database file

[June7]

Why do John records show same month in both fields of each record?

I still don't understand why Feb shows in both output months.

One way to pull data from another record involves nested query. Example:

SELECT Tablo1.*, (SELECT TOP 1 B_pay FROM Tablo1 AS Dup WHERE Dup.EmpName=Tablo1.EmpName AND Dup.ID<Tablo1.ID ORDER BY ID DESC) AS BP
FROM Tablo1 WHERE B_month = 3;

Or is this what you want:

SELECT Tablo1.EmpName, Tablo1.A_pay, Tablo1_1.B_pay, Tablo1.A_month, Tablo1_1.B_month
FROM Tablo1 AS Tablo1_1 INNER JOIN Tablo1 ON (Tablo1_1.B_month = Tablo1.A_month) AND (Tablo1_1.EmpName = Tablo1.EmpName);

[mike60smart]

Hi

Your 1 table is not normalised

1 Employee receives Many Payments.

See the attached example

[attman]

Thanks for your help.
I solved the problem in a good way.

Private Sub cmdGetTax_Click()
DoCmd.SetWarnings False


DoCmd.RunSQL "SELECT  query1.amonth AS Month,Table1.EmpName, CCur(0) AS A_pay, CCur(0) AS Bpay INTO table_Temp " & vbCrLf & _
"FROM Table1 INNER JOIN query1 ON Table1.EmpName = query1.EmpName " & vbCrLf & _
"GROUP BY Table1.EmpName, query1.amonth, 0, 0, 0, """" " & vbCrLf & _
"ORDER BY Table1.EmpName;"


DoCmd.RunSQL "UPDATE table_Temp INNER JOIN Table1 ON (Table1.EmpName = table_Temp.EmpName)" & vbCrLf & _
"AND (table_Temp.Month = Table1.amonth) SET table_Temp.A_pay = [Table1].[A_pay];"


DoCmd.RunSQL "UPDATE Table1 INNER JOIN table_Temp ON (table_Temp.Month = Table1.bMonth)" & vbCrLf & _
"AND (Table1.EmpName = table_Temp.EmpName) SET table_Temp.Bpay = [Table1].[Bpay];"
DoCmd.SetWarnings True
DoCmd.OpenTable "table_Temp"
End Sub

and query1

SELECT Table1.EmpName, Table1.amonth FROM Table1 GROUP BY Table1.EmpName, Table1.amonth UNION ALL SELECT Table1.EmpName, Table1.bmonth FROM Table1 GROUP BY Table1.EmpName, Table1.bmonth;

[mike60smart]

Hi
I would recommend NOT doing what you suggest and actually normalise the tables.

Otherwise, luck with the project.

[attman]

Hi
I would recommend NOT doing what you suggest and actually normalise the tables.

Otherwise, luck with the project.

payment amounts and tables are completely fake. I made a simplification to understand the problem.

the way the government agency requests the data as in the picture
There is no problem if there is only one employee group in the enterprise. But when there are employees belonging to 2 payment types, such a query is needed.
The requested data will not be used elsewhere. xml will be exported and sent once a month.
will not affect and use another file in the database