Filling a cross reference table - Loop?

[misscrf]

I have an issue with related records, which I have posted here thread701-1397905 . I am thinking I need to do a loop or for each or something, but not sure how to do it. Can someone please assist me?

Right now I have this query built, but it only gives me each customer related to themselves, lol. I understand that that is what it says to do. I know what I want to do, but I don't know how to express it.

Any help is greatly appreciated.

Thank you.

INSERT INTO tblRelated ( RMasterCustomerID, RelatedCustomerID )
SELECT Min(tblCustomers.ID) AS MinOfID, tblCustomers.ID
FROM tblCustomers LEFT JOIN tblAddress ON tblCustomers.ID = tblAddress.Vendor
WHERE (((tblCustomers.CustomerName) In (SELECT [CustomerName] FROM [tblCustomers] As Tmp GROUP BY [CustomerName],[Add1] HAVING Count(*)>1  And [Add1] = [tblAddress].[Add1])))
GROUP BY tblCustomers.ID;

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[fneily]

I just read the post you referenced. This is what it sounds like to me. First, get rid of autonumbers and integers as your primary key. You can do a search on the Access forums to see why. You have a customer with an unique ID. So Jane Doe's ID is ABC. So record looks like:
CustID FName LName Etc.
ABC Jane Doe etc.
She gets married and changes her name. Her record is now:
ABC Jane Smith etc.
So this table just has customer info with NO primary key. Thus duplicates with the unique key.
You then have your Order Table with the order info.
You then have a "Transaction" Table that has CustID, OrderID with both fields as the primary key (multi-field) and any COMMON data such as DateOfTrans, Amount, etc.

Now you can get easily get all related Name or Address changes records by querying on CustID from the main table and get the Orders for ABC by first filtering for ABC then joining that single record to the Transaction table which is then joined to the Orders Table.

You can also write a query or code to extract unique records (unique CustIDS) from the main table.

Then again, maybe I read it wrong.

 

[misscrf]

Sorry none of this will work. Thank you for your response. This is a shell that is fully developed. We do not have any control how we receive the data. The CID is for us to ensure there is a unique qualifier.

The orders for one Customer number's orders and another's have nothing to do with each other.

5 Customer records can be related, but only because they have the same customer name and address 1 field.

I just need a table that will list the minimum customer number of a group of related records, and then each customer number it relates to.

I.E.

Customer# CustomerName Add1 (from tblAddress)
123 Jane Doe 1 Main Street
124 Jane Doe 1 Main Street
154 Jane Doe 1 Main Street
167 Jane Doe 1 Main Street


My Cross Reference Table would look like this:

CrossID MasterCustomer# RelatedCustomer#
1 123 124
2 123 154
3 123 167

I have the table and relationships set up. Now I just want to populate this table. The query above just gives me this:

CrossID MasterCustomer# RelatedCustomer#
1 123 123
2 124 124
2 154 154
3 167 167


I was trying something with max but that wasn't working either. I was also looking into a loop function but I am not familiar with them.

Any advice would be great.

Thank you,

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[randy700]

I think you will have to manually build the cross reference table. With data as bad as you receive, there really isn't any way to tie them together.

Records such as these may appear to be the same customer:
123 Jane Doe 1 Main Street
124 Jane Doe 1 Main Street
154 Jane Doe 1 Main Street
167 Jane Doe 1 Main Street

But what about the record you mentioned earlier, if Jane gets married and moves?
204 Jane Smith 15 Green Street

How would you know it's the same customer?
I'd make a strong push to get better data from your source.


Randy