Update Count of records from one table to another based on criteria

[arvarunar]

I have two tables:

TrendMaster: EmployeeID, Joined_Period, Delay, Index, Branch

*Joined_period takes month eg: "April", "March"...

TrendSheet: Branch, April_Joined, April_Delay, April_Index, May_Joined...so on till March_Delay, March_Index

I need to update April_Joined with: Count of employees in TrendMaster for whome Joined_period is April and these need to be grouped as TrendMaster.Branch=TrendSheet.Branch

The query that i thought should work is:

UPDATE TrendSheet INNER JOIN TrendMaster ON TrendSheet.Branch = TrendMaster.Branch 
SET TrendSheet.April_Joined = ( Select Count(*) from TrendMaster where TrendMaster.Joined_Period='April')

However, I get an error "Operation must use an Updateable Query"
Please suggest!

[arvarunar]

Got the answer!
For anyone else who needs this, posting the query that worked:

UPDATE TrendSheet INNER JOIN TrendMaster
    ON TrendSheet.Branch=TrendMaster.Branch
   SET April_Joined=DCount("[EmployeeID]","TrendMaster","[Joined_Period]='April")

Thanx!

[NeoPa]

A perfect example of why the CODE tags are so important. Whatever you got to work, wasn't that. It's missing the last closing apostrophe (single-quote) after 'April.

Thank you for having the courtesy to post back with your solution though.

[NeoPa]

@arvarunar
I suggest you decide never to do such an update as it makes your database non-normalised. See Normalisation and Table structures.

If you decide that, after all you must, for whatever reason, then the approach you have suggested is about as good a one as you'll find. This is only an issue in Access mind. Most database servers don't suffer from this restriction and your original code concept would work fine.

Please think very carefully about your approach though. It is strongly recommended against (as you'll understand better when you've read the linked article).

[arvarunar]

@NeoPa
That apostrophe got missed somehow...I posted the same question on other forums...probably while copy pasting it got out...

Actually this is a very simple data and can be maintained and tracked easily using Excel. I chose to create the trend sheet in single click out of the data, thats why using access.

Also there are limits like this will be used by non-tech guys after I am done and resources are very limited. The output needs to be as is without negotiations or modifications.

Thanx for the article though...its universally useful..

There is another issue I am facing now...please help with that...

UPDATE TrendSheet INNER JOIN TrendMaster 
    ON TrendSheet.Branch=TrendMaster.Branch 
   SET April_Joined=DCount("[EmployeeID]","TrendMaster","[Joined_Period]='April'")

I am getting the April data through this, however the Branch does not match.

Output for this is:

Branch April_Joined
Ahmedabad 6
Mumbai 6
Chennai 6
Kochi

My TrendMaster had 6 records 1 for Ahmedabad, 3 for Mumbai and 2 for Chennai. This query totalled all and updated the total to all branches found. However, I need a group by effect!
Please suggest!

[NeoPa]

Let me first draw you attention to the edit comments I keep attaching to your posts when I have to go in and change them. Please use the tags in future as this is not optional.

[NeoPa]

You need to change your DMax() call to look through only the records that match the Branch required :

UPDATE TrendSheet AS TS INNER JOIN
       TrendMaster AS TM
    ON TS.Branch=TM.Branch
   SET TS.April_Joined=DCount("[EmployeeID]",
                              "TrendMaster",
                              "[Branch]='" & TS.Branch & "') AND "
                              "[Joined_Period]='April'")