CountIf in Access - HELP

You can just use Group by in query.

Ex:

Please Login or Register  to view this content.

Result:
0.JPG

Same results using this SQL statement

Please Login or Register  to view this content.

There is no need to reference table 1 as all the data is in table 2 and is counted using the group by functionality

Not a formula, it's a query. Since Table1 in your sample was just Distinct records from Table2. No need to reference Table1.

If you want to check with Table1, one way to do it is use nested selects.

I.E. Select Order Number from Table1. And Left Join on Order Number of the query I posted.

Since Firewall is preventing me from posting query... see attached text file and image.

1.JPG

Alternately you can write it as...

Please Login or Register  to view this content.

Yes. Here is a link to Group By so that you will have a better understanding

https://www.techonthenet.com/sql/group_by.php

Don't use calculated field if you can help it.

What you are after is derived record from existing tables. Hence, our recommendation to use query to return records.

Use query designer, and then go to SQL view and paste in the query (replace column/table reference as needed).

And save the query. You are not altering the existing table at all.

You are thinking like Excel. But this is database.
There are some limited case where Calculated field is used in database, but not many.
Have a read of...
https://www.datanumen.com/blogs/one-...access-tables/
https://btabdevelopment.com/why-you-...in-the-tables/

Also, I don't think Access supports calculated field using another table.

To help you understand a Data Base, here is a reference that will help you to understand how to set up and manage.

http://www.accessmvp.com/strive4peace/

So the query I showed does that...
It does not alter existing tables at all.

Here's steps.
Go to Create Ribbon tool -> Query Design -> Close the dialog window.
Go to SQL View (in Results section of ribbon) and select SQL View.
Paste in the query (from post #7).
Hit Run button.

You will see result like below.
0.JPG

If you want to name Expr1001 as Order Count. Alter query to...

Please Login or Register  to view this content.

And save query.

See sample db attached.

Here is a database zip file that replicates your tables and a query that sums the values that CK and I have been trying to explain to you.

But "why" is the question here. It's really bad practice to store derived/calculated value in a database table.

Hence, we've been trying to show you how to do it using query.

What end result (or process) that you are after, makes it necessary for you to store calculated value in a table?

You already have that in previous posts.

but rather deleting all of the values in my table

I'm guessing that by above, you mean that query isn't returning any data. If that's the case, it means that no item in Table 1 match "EXACTLY" to items in Table 2.

The query syntax is correct and works when there are matching Order Number found on both tables (as demonstrated by two sample db files).
I'd recommend you query each table independently and export it out to csv, and compare returned strings to ensure that there are exact matches found.

Oh and if you want to retain ALL items from Table 1. You should use Left Join instead of Inner Join.

. But the query, as presented, is deleting my data

This simply cannot happen with queries we provided. As query only returns record set that matches criteria, and does not alter source tables in any way.

Best of luck finding your answer.

I agree with CK. I am guessing that while you believe that you have matching data, you really don't. Capitalization of one and the other could be an issue. Spaces before or after one set of values may be the issue. Your data may not be formatted in the same manner. We have attached sample data showing you that it does work. You have only told us it does not work. Why not show us your database with the two tables. Put only a representative amount of records in each. Zip the file and upload to this site by clicking on the Go Advanced button at the bottom of the reply window. Follow instructions after scrolling down to Manage Attachments.