lookup not working with linked table - returns ID not field value

[sfb]

Hi everyone, first post here, would be grateful for any forum resources you can point me to that may help. so i have a database broken into frontend and backend. there is a linked table (exists on the backend, is linked on the frontend), called 'master customer list' (should probably be called 'contacts' list as it contains all contacts, not just active customers).



there is also a linked table called 'pickups' which exists in the same way (on the backend, linked in frontend). this keeps track of our service calls to a given customer

'pickups' table looks thru 'customer query' which filters the 'master customer list' table by a status field and only shows active customers. this works in the backend perfectly, but the frontend has a nice form i would rather use so i can look at other customer properties while entering data in a subform.

anyway 'pickups table' on the frontend is broken
'master customer list' and 'pickups' are both linked to the backend.
'customer query' exists on both the backend and frontend (queries cant be linked) and i believe these queries are identical (at least for the fields i need).

so in pickups table I try to look up customer name from customer query using the following command:
SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query];

this works fine in the backend version of 'pickups' table and returns the actual company name values ina nice drop-down combo box. when i look at the fronend version of 'pickups' table, the IDs are listed instead of the customer name, and the lookup field is always blank upon opening. this is where it gets wierd. if i paste the
SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query];
here, it shows the customer name, but there are literally 100s of dupes of each customer. if i close table and reopen it, it does not save changes, the ID is shown instead of the customer name, and the lookup table is again blank.

what gives? any suggestion? ie got an older version of the database (with old, obsolete data tho) that works just fine. just thinking theres gotta be a bad relationship somewhere or bad syntax. any help? thanks in advance.

Worth mentioning that SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query]; in pickups query in the broken frontend returns 917756 rows (when it works, until i close the table, and all changes are lost). Master Customer List contains 2044 rows, customer query contains 449 rows. 2044*449 = 917756, so something is looping and running the query for every table entry. (and i still dont know why the linked Pickups table wont save, maybe the 917756 rows make it bug?)

[sfb]

if this helps, here are the customer queries (which appear to work fine, until i try to lookup at them from the pickups table

old backend, pickups table works

SELECT [Master Customer List].ID, [Master Customer List].[Supplier Name], [Master Customer List].Status, [Master Customer List].[Main Region]
FROM [Master Customer List]
WHERE ((([Master Customer List].Status)=7 Or ([Master Customer List].Status)=8 Or ([Master Customer List].Status)=13 Or ([Master Customer List].Status)=14));

old frontend, pickups table works

SELECT [Master Customer List].ID, [Master Customer List].[Supplier Name], [Master Customer List].Number, [Master Customer List].Street, [Master Customer List].City, [Master Customer List].[Main Region], [Master Customer List].Region, [Master Customer List].Type, [Master Customer List].[Contact Name], [Master Customer List].Title, [Master Customer List].Phone, [Master Customer List].Fax, [Master Customer List].State, [Master Customer List].Zip, [Master Customer List].[Pickup Frequency], [Master Customer List].[Last Pickup Date], [Master Customer List].[Next Pickup Date], [Master Customer List].Comment, [Master Customer List].[Barrel Type], [Master Customer List].[Barrel Placement Date], [Master Customer List].[Number of Barrels], [Master Customer List].Status, [Master Customer List].[Est gal/week]
FROM [Master Customer List]
WHERE ((([Master Customer List].Status)=7 Or ([Master Customer List].Status)=8 Or ([Master Customer List].Status)=14));


new backend, pickup table works

SELECT [Master Customer List].ID, [Master Customer List].[Unique Name], [Master Customer List].[Supplier Name], [Master Customer List].Status, [Master Customer List].[Main Region], [Master Customer List].[SF Rep]
FROM [Master Customer List]
WHERE ((([Master Customer List].Status)=5 Or ([Master Customer List].Status)=6 Or ([Master Customer List].Status)=7 Or ([Master Customer List].Status)=8 Or ([Master Customer List].Status)=12 Or ([Master Customer List].Status)=13 Or ([Master Customer List].Status)=15 Or ([Master Customer List].Status)=16 Or ([Master Customer List].Status)=18));

new frontend, pickups table broken on SELECT [Customer Query].ID, [Customer Query].[Unique Name] FROM [Customer Query]; lookup

SELECT [Master Customer List].ID, [Master Customer List].[Unique Name], [Master Customer List].[Supplier Name], [Master Customer List].Street, [Master Customer List].City, [Master Customer List].Region, [Master Customer List].Type, [Master Customer List].[Contact Name], [Master Customer List].Title, [Master Customer List].Phone, [Master Customer List].Fax, [Master Customer List].State, [Master Customer List].Zip, [Master Customer List].[Pickup Frequency], [Master Customer List].[Last Pickup Date], [Master Customer List].[Next Pickup Date], [Master Customer List].Comment, [Master Customer List].[Barrel Type], [Master Customer List].[Barrel Drop Date], [Master Customer List].Status, [Master Customer List].[Est gal/week]
FROM [Master Customer List], [Master Customer List] AS [Master Customer List_1]
WHERE ((([Master Customer List].Status)=5 Or ([Master Customer List].Status)=6 Or ([Master Customer List].Status)=7 Or ([Master Customer List].Status)=8 Or ([Master Customer List].Status)=12 Or ([Master Customer List].Status)=13 Or ([Master Customer List].Status)=15 Or ([Master Customer List].Status)=16));