Hello,

I am trying to figure out where I'm going wrong (as is everyone else I'm sure). :confused:

I have fld3 in tbl1 that I need to display a total count of fld1, fld2 in tbl2 that matches data in fld1, fld2 in tbl1.



Here are a few codes that I attempted to place in fld3's "Default Value" box in table design view, but Access did not accept:

SELECT count([tblAM].[fldPO]), [tblAM].[fldPO], [tblAM].[fldLine] FROM tblAM INNER JOIN tblPur ON [tblAM].[fldPO] = [tblPur].[fldPO]

SELECT count([tblAM].[fldPO]), [tblAM].[fldPO], [tblAM].[fldLine] from tblAM, tblPur WHERE [tblAM].[fldPO] = [tblPur].[fldPO] and [tblAM].[fldLine] = [tblPur].[fldLine]

SELECT COUNT fldPO, fldLine FROM tblAM WHERE fldPO = tblPur.fldPO AND fldLine = tblPur.fldLine

SELECT fldPO, fldLine FROM tblAM COUNT (*) Where fldPO = tblPur.fldPO AND fldLine = tblPur.fldLine


Could someone look at my db and syntax and tell me what I'm doing wrong?

You probably should have simply continued your other thread. In any case, you can't use SQL directly in the control source of a textbox, and certainly not in the default at the table level. I think it's a big mistake to try and save those 2 fields. What are you going to do when someone makes a mistake and deletes an entry from the asset table? Any values you've calculated and saved from that point forward are then wrong. If you insist, try a DCount function instead. Info on syntax here:

http://www.mvps.org/access/general/gen0018.htm

Could someone look at my db and syntax and tell me what I'm doing wrong?
Trying to store a calculated value in a column?

Smug answer aside, I really, really, really wouldn't store a calculation against data in your table in a column, what happens when someone changes the values or inserts more rows that match the criteria? If you build into a form routines to trap this and update the column, what happens if someone edits the tables directly?

You are much better off displaying the calculation result directly on your form/application as a result of a query against your data than querying a value in a column.

I know everyone keeps saying it shouldn't be done, and if it is really that bad then maybe not.

But I've seen it done before and the numeric value stored in the fld1 constantly updated itself when someone updated/edited rows in tbl1.

In response to tehNellie's question about what happens when someone changes the values or inserts more rows that match the criteria? - That was the idea. If someone added a new row with matching criteria, then the quantity would increase with the new row. If someone edited a row so that criteria no longer matched, then the quantity would decrease.

I planned on locking down the tables anyways so that users couldn't enter/edit data except through predesigned forms.

Values like "Quantity Received" and "Quantity Outstanding" will not update themselves in Access tables (you could probably do it with triggers in SQL Server). I would love to see a sample that does. I suspect what you've seen is the result of code running in the background that updated the values. Here's a discussion on the general topic:

http://allenbrowne.com/AppInventory.html

Note that the discussion continues under the sample code.

I think I'm starting to understand.

My next question, then, is it possible to create a query, that, when ran displays a count on a form? (So it isn't coming from a table field, but is it's own standing display? Did that make sense...:rolleyes:)

-----------------------

Correction,

Because I realized that the information received wouldn't be enough....

Is there a way to create a report >> that runs a query >> that displays those rows where the query count and the fldQty (quantity received) do not match?

I posted queries that would do that on your other thread yesterday.

I apologize. I probably didn't understand where you were going with that. I'm really new with Access and DB design.

I have fld3 in tbl1 that I need to display a total count of fld1, fld2 in tbl2 that matches data in fld1, fld2 in tbl1.Are you looking for something like this....??

Paste the SQL I posted into 2 empty queries, naming the first one as noted, and run the second one.

Are you looking for something like this....??

Hello AJ,

Thanks for the test, but it wasn't exactly what I was looking for.

brsawvel