Query slow/hanging

[tweetyksc]

I don't know what the problem is with this:
I have two tables, one is a billing table and one is a commission
table. I need to create queries/reports with this data.

Each have a field "order number" and "line number"; the order number may be on each table multiple times, with different line numbers (order lines).

In order to match these up, I created two queries from each table with a field called "Ordermatch" which combines the order number/line number fields. "Linematch:[order number] & [line]"

The individual queries run just fine, but when I try to match them up in the main query to run data I get "recordset not updatable" in the status line, and it takes forever to run...then some data shows up but then hangs.

I didn't create this database...checked the tables and changed the "order number" field to indexed/duplicates OK. Changed
the line number (which was double) to text as Order number field is. I had done a calculation in the underlying order table, even
taking these out and just joining the two tables with nothing causes it to bomb. HELP!

I have checked the Access performance for tips but there were no problems found.

SQL for query making the join:
-----------------------------------------------------------
SELECT qryMBProofBase.[Sales Area], qryMBProofBase.[Employee Number], qryMBProofBase.[Business Partner], qryMBProofBase.[Order Number], qryMBProofBase.Line, qryMBProofBase.Item, qryMBProofBase.[Product Line], qryMBProofBase.[PL Sales], qryMBProofBase.[PL Cost], [PL sales]-[pl cost] AS [GP$], ([PL Sales]-[PL Cost])/[PL sales] AS [GP%], qryMBCommbase.Com_Sales, qryMBCommbase.[Com Amount], qryMBCommbase.[Com %]
FROM qryMBCommbase RIGHT JOIN qryMBProofBase ON qryMBCommbase.Linematch = qryMBProofBase.Linematch;

 

[tweetyksc]

Thanks for your reply.
But the concatenated field does show the combination of order and line, which would be (ideally) a unique field.

I say ideally because I need to start all over again with this. What I found is the query was running slowly because the table with the orders has only one line per order/line# combo; but the commisions has MULTIPLE lines for each order/line due to additional lines for "excess commission". This throws off everything.

Employees have one employee number in the orders table (tied to orders/lines), but that number PLUS another employee number IF they get excess commission is tied to orders/lines in the commission table, hence the duplicate orders/lines.

Looks like I'm going to have to create some lookup tables to run reports through because I need to run reports by some fields that are only in the orders table.
Hopefully I can find my way through.