CODEWITHSUNDEEP
sqlms-accessvba
Nick King
Nick King

Reputation: 200

MS Access - How to Increment number in a different table?

I am very new to MS Access and my question is that if I have two tables and Table1 have 10 entries and that table have a drop down list of yes and no and a name field(multiple entries with the same name). Now in Table2 there is a Name field(no multiple entries) and total field. So whenever user select "yes"from that drop down menu from Table1 then the total field in my Table2 should get increase by 1.So is it possible to do it this way or is there any other and simple way to tackle this problem? Thanks in advance.

Upvotes: 3

Views: 1391

Answers (1)

Blenikos
Blenikos

Reputation: 743

You can't do that directly inside the table in MS Access. In MS Excel you can create a formula that counts the TRUE-FALSE flags in a table and gives that count using COUNTIFS but there is no option to do that directly in a table field in Access (writing a formula). Access is not working like that.

You need a SQL query in order to do what you want. However MS Access doesn't allow aggregation functions on UPDATE queries and that means you cannot use GROUP BY with UPDATE.

What you could do is write a macro that calls 3 different SQL queries to do what you want in a similar workaround way

We have the following example:

I will use the Tables: Table1(ID, Name As Text, Check AS Boolean) Table2(ID, Name_Total as Text, Total as Integer)

I have created and saved the following Queries:

Query1

SELECT * INTO Temp_Table
FROM 
    (SELECT Name, Count(Name) AS Count_Total 
    FROM Table1, Table2 
    WHERE Name = Name_Total AND Check = True GROUP BY Name) AS [Counter];

Query2

UPDATE Table2 
INNER JOIN Temp_Table ON Table2.Name_Total = Temp_Table.Name 
SET Total = Count_Total;

Query3

DROP TABLE Temp_Table;

The first query creates a temporary table that counts in Table1 for every name how many times appears in the table. The second Query updates the Total Values on Table2 with the values found and put on the Temp_Table The third query just deletes the Temp_Table

Every time you want to update your table2, you have to run those queries in that order.

Now you just have to put that queries (in correct order) in a macro and run that macro manual or on Database open or whatever you want it to run (when you press a button on a Form)

It is not exactly what you wanted but I hope it will help working around your problem.

Upvotes: 3

Related Questions