Update Table Column Based on Count Query (1 Viewer)

[accessorater77]

I've attached the database in question. It only contains 2 tables (Tally, Tasks) and 1 query (Counter).

The query "Counter" is just a simple Yes/No counter. It is getting the data from the "Tasks" table from a Yes/No column, and I am trying to display the data from the query in the "Tally" table.

The problem is that the only way to display the updated data in the "Tally" table is to set the column as a list box, and then you can choose the updated data as the only choice on the list.

In the "Tally" table, I have the 'Completed' column set as a list box, and the 'Incomplete' column set as a text box. The 'Completed' Column will show the update from the query via the list choice, as mentioned above, but the 'Incomplete' column will show no update at all.

I'm thinking this is a much more simple problem than I am making it sound. Please, if you will, just open the database and check it out--as it's a very basic database.

Thanks in advance.

P.S. Apologies if this should've been posted in the Query Forum instead.

 

[plog]

I am trying to display the data from the query in the "Tally" table.

That's not how databases are to work. You don't store calculated data....you calculate it when you need it. So instead of moving data around, just make a SELECT query and reference it.

Also, how come your two tables aren't related?

 

[accessorater77]

That's not how databases are to work. You don't store calculated data....you calculate it when you need it. So instead of moving data around, just make a SELECT query and reference it.

Could you elaborate on this idea? Or point me in the right direction?


As for no relationship...I could create one, but didn't see that being the solution to updating the columns. This database is just a mock snippet of something I'm building that is much larger, with related tables.

 

[plog]

Could you elaborate on this idea? Or point me in the right direction?

Let's use a Sales example. Suppose you had this data:

SalesPeople
SalesID, SalesName
1, Fred
2, Sally

Sales
SalesID, SalesDate, SalesAmount
1, 1/1/2016, 26
1, 1/2/2016, 13
2, 1/1/2016, 8
1, 1/3/2016, 11
2, 1/5/2016, 21

Now if you wanted total sales by salesperson, you wouldn't add a 'Total Sales' column to SalesPeople and run an update query to populate it based on the totals in Sales, you would run this query:

SELECT SalesName, SUM(SalesAmount) AS SalesTotal FROM SalesPeople LEFT JOIN Sales ON SalesPeople.SalesID = Sales.SalesID GROUP BY SalesName;

And it would give you this data:

SalesName, SalesTotal
Fred, 50
Sally, 29

You wouldn't store it, you would just use that query when you wanted sales total. That way, as new sales come in, or old sales gets updated (via return, or data corrections) the totals are always correct based on the underlying data.

You need to do the same thing in your database. Calculated your totals in a query, then refer to the query when you want those totals--don't store them.

 

[accessorater77]

Okay, thank you. I get it.

You just have to run (or "use"), the Query every time you want the updated amount. Meaning, for my database, I would just run the "Counter" query when I want the update.

You can't display the updated numbers in a table, because that's not what databases (especially tables) are used for.

I understand all of this now, thank you.

BUT, my problem is that I have to construct this database to be very user friendly, and getting someone to run a query out of the left-sided navigation menu would not be preferable, or even possible. Perhaps a command button that runs the query and updates the cell of the table?

 

[plog]

If you intend to have users of your database you do things through forms and reports. Users shouldn't interact with tables or queries. You would build forms to allow them to get to all the data they need.

 

[accessorater77]

[Solved] Update Table Column Based on Count Query

Awesome. I already had a form made that was using that query as the source. The form is embedded into a navigation form, therefore every time one clicks on the tab to pull the report, it runs that query by default.

Thanks, again.