Search results

How many fields exactly have to match from the two tables? Is there a primary key in each table?

I just copied your SQL into a new query. Uploaded both your XL tables and imported them to Access 2002. The query produces 9999 records as predicted. That is, 3 times the expected records. If you add another field, Table2.Brand and set the criteria to "Group" it works.

CarysWW: just read your sig and love that you're a fan of Douglas Adams. Sorry, this is not on topic.

Sorry, forgot the important ending... After fixing the 191 table with those found in 221; and the reverse; and eliminating the duplicates and the one with no permit number... Do a Duplicates query on the 221 table. There are 4 #1107970 for example.

1186200 1010124 (twice) = 3 in 191 that don't match. 1186281, 0.003, 1186268, 1119755, 1119755 (again), and 1185707 don't match in 221 table. Method: First do a matched query of 191 and 221, joined by permit#...result is 118 records that are in both. Then use that query result to perform an...

On second look, I may be mistaken. 221 has 0.003 as first item after sort; 191 has a blank permit# first after sort. To sort, I used table datasheet view and used AZ to view the sorted result (by Permit No), then didn't save result.

Some of the records are not formatted as Text, some not as Numbers. Make all Permits the same format. Try doing an alpha sort and you'll see.

In the Query properties of the calculated field, set the format to hh:nn:ss

zibzaba: To recap, you have a FARMER_ORDERS table that is not linked to the LABOR_EXPENSES table or to CUSTOMER_ORDERS or to the two Queries that total your Office & Transport expenses. Thus each is reported uniquely: Labor 1, farmer 1, customer 1, amount 1, amount 1; Labor 1, farmer 2...

Does your query look like this at all?

So for each MemID, you want a Sum of the ConAmt for each different ConDte--are you not getting this with a simple Design View query?

Yes, or without SQL: Glad you solved it.

It looks as though the SELECT and FROM rows in your SQL should precede the EXISTS row. But check out this site: http://www.techonthenet.com/sql/exists.php to make sure you follow the syntax.

Since this question was not answered, I'll chip in that you are likely not using the Totals feature and "Group By" whatever field you want the hours totalled. If all you require is a total for all fields that have a WeekEnding of a particular date, use that field with "Where" in its Total row...

Yes, use as one of the matching queries, the results of your Unmatched Query. The two queries can be run sequentially from the form but I can't think of a shorter method. Brian?

I'll withhold my 'you're welcome' until I hear that the result did indeed include all required records. I should have asked, for example, what the two queries being compared used as criteria for inclusion. Perhaps as a first step, the correctly matched records would need to be excluded from...

mikd1970: Sorry, but I missed notification of the arrival of your private message (a week ago). Perhaps it has since been solved, but if not, I suggest that if the form's list box is called frmCriteria!listbox1 you can use as the Criteria row in the query Forms!frmCriteria!listbox1 (The query...

A simple query in design view, including both queries (without joining them), and the similar field from each; using as criteria in the less reliable query field: Like "*" & [Queryname.Queryfield] & "*" would get you a comparison.

Kryst51 suggested using a form but the parameter can just as easily be stated in the Design View without a form. Like "*"&[parameter]&"*" (when prompted for parameter it populates both the Criteria and Or rows.

I realize I'm likely 'way out of line by interrupting, your initial post indicated you were using "red, blue" to mean "red" or "blue", so I'm going to venture that even without SQL, VBA, or whatever, the basics for criteria remain: