build compatibility list

[jdlevaccess]

Hey guys, just joined and wanted to see if anyone can help me out here. I have a table named tmo_details with thousands of records that have a code (not unique). This code is compatible to other codes. i.e. code "2ab" could be "1ab","3cf", "8ht". I was thinking to build a compatibility table called compatibility like this.

code	compatible codes
2ab
2ab	1ab
2ab	3cf
2ab	8ht

Basically I want to write an update statement that if the code = code then reconcile OR if code = any of the compatible codes then reconcile.

I hope this makes sense, and one of you gurus can assist with this approach or have any other suggestions.

[orange]

What exactly is your question?
Did you try any approaches as a test? What results?

Your sample deals is a little abstract. Here's an example I think matches you question, but only you know for sure.

Suppose you had a list of "approved" words and some other words that were "also used for the same general meaning".

Big could be large, huge, gigantic, enormous, vast so you could have

Approved   Alternate with same meaning

Big              Large
Big              Huge
Big              Gigantic
Big              Enormous
Big              Vast

Sounds like it should work:

Select AlternateWord from Table where ApprovedWord ="big" to list all alternates

Select ApprovedWord from Table where AlternateWord = "vast" to find the approved word from an alternate word

[smithse]

Hi,

I guess I am asking the same q as orange? What exactly is the question, and which 'way' must the compatibility work? In the example you gave, 2ab is compatible with 1ab and 3cf. Does this mean that 3cf is compatible with 1ab?
By reconcile, I am assuming you want to tidy up your list and get rid of duplicates and nulls? If compatibility is one way, i.e. 'compatible codes' are only compatible with 'code' then I would create a distinct list of all the codes, and query each compatible code. You could do this in a 'make table' query.

[jdlevaccess]

Hi,

I guess I am asking the same q as orange? What exactly is the question, and which 'way' must the compatibility work? In the example you gave, 2ab is compatible with 1ab and 3cf. Does this mean that 3cf is compatible with 1ab?
By reconcile, I am assuming you want to tidy up your list and get rid of duplicates and nulls? If compatibility is one way, i.e. 'compatible codes' are only compatible with 'code' then I would create a distinct list of all the codes, and query each compatible code. You could do this in a 'make table' query.

The question is how do I update the column "reconciled" in my table if the tbl1.code = tbl2.code OR if tbl1.code = tbl2.any of the compatible items The compatibility must work both ways. So you are correct, 3cf is compatible with 1ab. When I mention reconciliation, this is against a point of sale system records. There is a file from a vendor and I need to mark which records have been paid (reconciled) and which have not (un-reconciled). I can then submit to the vendor which records have not been paid.

I have attached the database with the tables that are applicable. There are 3 tables. I need mark final_ledger.reconciled if final_ledger.[vendor sku] = final_tmo.[plan code] or final_ledger.[vendor sku] = compatibility.compproducts

[orange]

What are the Primary keys for your tables?
How are the Tables related?

With this query I find 113 records [ This is for final_ledger.[Vendor SKU])=[compatibility]![compproducts]

SELECT final_ledger.[Vendor SKU], compatibility.compproducts, final_ledger.[Tracking #], final_ledger.pid
FROM final_ledger, compatibility
WHERE (((final_ledger.[Vendor SKU])=[compatibility]![compproducts]));

With this query I get 834974 records which is more than you have, so this is some sort of Cartesian Product]

SELECT  final_ledger.[Vendor SKU], final_tmo.[Plan Code], final_ledger.pid, final_ledger.[Tracking #]
FROM final_ledger, final_tmo
WHERE (((final_ledger.[Vendor SKU])=[final_tmo]![Plan Code]));

In an attempt to remove duplicates I set up this query that gives 2803 records

SELECT DISTINCT  final_ledger.[Vendor SKU], final_tmo.[Plan Code], final_ledger.pid, final_ledger.[Tracking #]
FROM final_ledger, final_tmo
WHERE (((final_ledger.[Vendor SKU])=[final_tmo]![Plan Code]));

You may want to restructure your tables, or you may want to define your requirements in more detail.

I don't think the set up follows your initial post re compatibility.

[jdlevaccess]

Orange, I appreciate your assistance. For these items to be "reconciled", the [tracking #] and [vendor sku] must match final_tmo.[service number] and final_tmo.[plan code] OR compatible items in the compatibility chart must match. You mention that I might want to restructure my tables. Do you have any suggestions for this to work properly? I know this is possible, but I'm just having a hard time figuring it out.

[orange]

It is critical for table design and queries to work properly to know exactly which fields in 1 table must match fields in a second table.

You didn't mention Tracking# in your post and that's what is causing confusion.

You should research Normalization. Also you should use a Naming convention that does NOT allow spaces and special characters in field or object names.

For these items to be "reconciled", the [tracking #] and [vendor sku] must match final_tmo.[service number] and final_tmo.[plan code]
OR compatible items in the compatibility chart must match.

You must be explicit
what fields in what tables must match?

Why are the names different in each table?

[jdlevaccess]

The reason for the spaces in the tables is due to the fact that there is a front end for a person to import these files from excel files. Sorry I didn't mention tracking # in the previous posts. Honestly didn't think it was important, as I was looking for a concept to accomplish the goal (being to match tracking # & vendor sku to plan code] or compatible codes.

To reiterate, i need to mark final_ledger.reconciled to '1' when final_ledger.[tracking #] = final_tmo.[service number] and final_ledger.[vendor sku] = final_tmo.[plan code] OR final_ledger.[vendor sku] = compatible codes (from compatibility table). This makes sense in my head, but I'm going to give an example from the database that I just re-uploaded. final_tmo[service number] 4012191832 has [vendor sku] NWPHPBDL. In final_tmo, the same service number (tracking # in final_ledger) is inputted as final_tmo.[plan code] MBBPHPBDL (same as final_ledger.[vendor sku]). If you open the compatibility table you will see that NWPHPBDL can also be MBBPHPBDL. How do I update final_ledger.reconciled? Does that make more sense? sorry if this is getting confusing.

[orange]

I think there is some restructuring to be done in order to make your compatibility work. Just a hunch at this time.

From what I see you only have 3 compatibility records. But it isn't yet clear what your tables represent and how they relate.

Your tables are not normalized from what I can see.
Tell me exactly how 4012191832 relates to whatever you're trying to do.

I think I sorted through the compatible products in the

Using your last statement

If you open the compatibility table you will see that NWPHPBDL can also be MBBPHPBDL.

I created a query finalLedgerVendorSkuAndCompatibleProductsQuery

and showing the results in the related Results jpg.

I still do not understand the tables. I think your compatibility table should only have 2 fields (but I don't know what you're trying to represent with all the fields).

What records exactly should have reconciled set to 1? That's just a question to see what your expectations are.

[jdlevaccess]

final_tmo and final_ledger [reconciled] should both be set to 1for final_ledger.[tracking #] with NWPHPBDL as the [vendor sku] and tmo_final.[service number] 4012191832 with [plan code] MBBPHPBDL. There would only only be 2 fields in the compatibility table ([plan code] & [compproducts] , but had description in there only for reference.

[orange]

I still do not understand why you have a reconciled field/flag in 2 tables. I think your design could get you into a lot of trouble.

You should research Normalization and read this article on Relational Database Design especially the one field, one fact sort of thing.

http://forums.aspfree.com/attachment...achmentid=4712

I realize you're showing only 5 posts, but I sense you have more experience than that. Can you tell me what the tables represent, all the concepts.

I think you should build a proper model and get your tables designed/structured properly. The entity relationships diagramming here would help you with that.
http://www.rogersaccesslibrary.com/T...lationship.zip

You will improve your set up by putting your tables/application through the ERD process.

I am attaching another query result that seems to say you have several concepts in these tables.

With a Update query you can only update one table at a time, so you would need 2 queries. However, as you can probably tell, you would normally only have 1 table containing the "reconciled" fact.

Anyway good luck with your project.

[smithse]

Hi guys,

Ok, so there have been a number of posts since I have last read this one, and I am just catching up. I cant help but notice there seem to be a number of fundamental issues with your design of a 'compatibility table'. If you are indeed wanting to build a compatibility table, it should only be able to operate in one direction.

Take your first example:

code	compatible codes
2ab
2ab	1ab
2ab	3cf
2ab	8ht

2ab is compatible with 4 values: null (really?), 1ab, 3cf and 8ht. As you clarified in a later post, '8ht' is compatible with 2ab, null, 1ab and 3cf.
This is going to create nothing short of a nightmare.

If you wanted to create this sort of compatibility table using these values, you would have to structure your table so that it reads something like this. Note, that I exclude nulls, or empty values:

code	compatcode
2ab	1ab
2ab	3cf
2ab	8ht
1ab	2ab
1ab	3cf
1ab	8ht
3cf	1ab
3cf	2ab
3cf	8ht
8ht	2ab
8ht	1ab
8ht	3cf

This will avoid your sort of circular type reference that you have going on at the moment. Does that help, or just confuse the issue? Also, you'd need to manage where your compatibility is entered as the code itself i.e. 1ab = 1ab.