Updating 1st record of duplicate data only (First Post)! Thank You!

[zsalem]

All

Below is what I am trying to do.

I need to update a table (Table A) with data from another table (Table B) but only update the first row or 1 row of each grouping of rows.

Table A

IDNmbr Empl_Numb PEREND Names PayType Visits
1 10047 9/16/2016 JohnDoe REGULAR
2 10047 9/16/2016 JohnDoe Vacation
3 10047 9/16/2016 JohnDoe PTO
4 10047 9/23/2016 JohnDoe REGULAR
5 10047 9/23/2016 JohnDoe HOLIDAY
6 10047 9/23/2016 JohnDoe SICK
7 10099 9/16/2016 ZachYork Vacation
8 10099 9/16/2016 ZachYork PTO
9 10200 9/16/2016 JessTill Vacation
10 10200 6/23/2016 JessTill PTO
11 10200 9/23/2016 JessTill TRAVEL
12 10500 9/16/2016 PaulDown REGULAR

Table B
empl_no emp_name Visits PEREND
10047 JohnDoe 13 9/16/2016
10075 RobBate 20 9/16/2016
10020 JackFlash 29 9/16/2016
10099 ZachYork 24 9/16/2016
10200 JessTill 20 9/16/2016
10500 PaulDown 22 9/16/2016
10047 JohnDoe 25 9/23/2016
10200 JessTill 25 9/23/2016

Table A - What I need it to look like after update.
IDNmbr Empl_Numb PEREND Names PayType Visits
1 10047 9/16/2016 JohnDoe REGULAR 13
2 10047 9/16/2016 JohnDoe Vacation
3 10047 9/16/2016 JohnDoe PTO
4 10047 9/23/2016 JohnDoe REGULAR 25
5 10047 9/23/2016 JohnDoe HOLIDAY
6 10047 9/23/2016 JohnDoe SICK
7 10099 9/16/2016 ZachYork Vacation 24
8 10099 9/16/2016 ZachYork PTO
9 10200 9/16/2016 JessTill Vacation 20
10 10200 6/23/2016 JessTill PTO 25
11 10200 9/23/2016 JessTill TRAVEL
12 10500 9/16/2016 PaulDown REGULAR 22

What my table looks like AFTER MY QUERY... (Counting the visits too many times.)
IDNmbr Empl_Numb PEREND Names PayType Visits
1 10047 9/16/2016 JohnDoe REGULAR 13
2 10047 9/16/2016 JohnDoe Vacation 13
3 10047 9/16/2016 JohnDoe PTO 13
4 10047 9/23/2016 JohnDoe REGULAR 25
5 10047 9/23/2016 JohnDoe HOLIDAY 25
6 10047 9/23/2016 JohnDoe SICK 25
7 10099 9/16/2016 ZachYork Vacation 24
8 10099 9/16/2016 ZachYork PTO 24
9 10200 9/16/2016 JessTill Vacation 20
10 10200 6/23/2016 JessTill PTO 25
11 10200 9/23/2016 JessTill TRAVEL 25
12 10500 9/16/2016 PaulDown REGULAR 22

Current Query
UPDATE [TableA] INNER JOIN TableA ON ([TableB].empl_no = [TableA].Employee_no) AND ([TableB].PEREND = [TableA].[PEREND]) SET TableA = [TableB].Visits
WHERE [TableA].IDNmbr IN (SELECT Min(IDNmbr)
FROM TableA
GROUP BY Employee_no,PEREND);


Any help is GREATLY appreciated. If I only knew in ACCESS what I know how to do in Excel and Excel VBA.

Willing to LEARN!

Thank you

 

[Minty]

You don't need to store that - simply link it by employee id in a query, why store it twice? In fact if it's already calculated to get into table B you shouldn't be storing it there either, you should always calculate it.

 

[zsalem]

My output table needs to show...

Multiple employees, the types of pay they received each week ending date, and the number of visits they did that week.

If I link up with Empl ID number it will repeat the visits because there is duplicate employee numbers due to multiple weeks.

I understand I may have incorrectly designed the table structures to begin with but is it not possible to do what I am asking?

Can you expound on what you're meaning?

thank you!

 

[Minty]

Your table doesn't need the visits in it - your create a query for your report that does include the visits. Looking at your desired output it will be very difficult to achieve directly in a query, from your current data.

In a report you can hide repeating values which would give you the desired output using this query; (Note: no need to store things in your table...)

SELECT DISTINCT TableA.ID, TableA.Empl_Numb, TableA.PEREND, TableA.Names, TableA.PayType, TableB.Visits
FROM TableA LEFT JOIN TableB ON (TableA.PEREND = TableB.PEREND) AND (TableA.Empl_Numb = TableB.EmplyNo);

Also you are storing the employee name in every table - you only need the employee id , join the final query to the employee table to get the name.

 

[jdraw]

zsalem,

You should research Normalization and Database design to help with the concepts involved.