Normalize DB

[Kenln]

I have an Access FE with MSSQL BE an Pass-Throughs to and iSeries (AS-400). Okay I am already over my head.

I would like to build a subform (data sheet) that allows the user to select vendor records. This will store the Vendor Number in an MSSQL table. Selecting the Vendor Number is easy, just add a combo box with the Row Source pointing to the iSeries table and have the Control Source as the Vendor Number in MSSQL. Perfect, works great, no problem.

Until...

I would like the user to have the ability to select by either Vendor Number or Vendor Name. If I use the above example substituting the Vendor Name for Vendor Number it still works great. Except, since the MSSQL table will have the Vendor used several times then the name appears several times thus NOT NORMALIZED. No big deal until the Vendor name is updated on the iSeries, all of the sudden the data no longer matches. Sigh...

Using the same subform concept I tried to use the Control Source for both combo's as Vendor No but the combo Vendor Name does not display, I tried no Control Source and ALL Combo Vendor Names show the last name entered (oops).

Another alternative is to update the (no longer normalized) table but it won't update (currently being addressed in another thread).

Is their something obvious that will make me feel foolish (I hope).

Thank you,

 

[The_Doc_Man]

OK, your problem is simple. You have a split database. I'm not familiar with the ins and outs of either, but I'm just about going to guarantee you can't establish Relational Integrity across the interface between the two machines. Without that capability, you are ... how delicately can I say this? Screwed.

Unless you can somehow restructure this so that all data potentially subject to Relational Integrity is always on the same machine, I don't know how to fix this in a way that isn't a supreme nightmare.

What authority do you have on keeping data together? Before anyone can propose a solution, I think we need to know how far you can bang on this to make it fit.

 

[Banana]

OK, your problem is simple. You have a split database. I'm not familiar with the ins and outs of either, but I'm just about going to guarantee you can't establish Relational Integrity across the interface between the two machines. Without that capability, you are ... how delicately can I say this? Screwed.

I could be wrong, but I think you could create relationships between two linked tables in the Access environment and thus constraint the data. Of course, you're correct that it's impossible to create relationships between two discrete databases, but in Access they're just another tables... no?

 

[Kenln]

Banana I was really hopping on that one. But... I got the same message.

As far as change I don't think so. The vendor information is on the iSeries and part of our business system. This application is external to add funcationality that we need.

 

[Banana]

When I think about it, I realize this is really just putting horse before cart. If two tables does not match up, you may not be able to make the relationship.

(I'm not sure what "same message" we are referring to?)

 

[Kenln]

Operation must use and updatable query.

 

[Banana]

Wait, that was for creating a relationship, not a query? For the linked tables?

I'd bet that the culprit is that Access has no idea where is the primary key and thus treat it as read-only. Open any linked table in design view and see if there's a primary key declared, or if any field are indexed.

 

[Kenln]

In the MSSQL tables yes, in the Pass-Through query no. Not sure how to set one.

Note 'Why is this not updatable?' thread.

 

[Banana]

PT queries, by definition, are non-updateable. You need to reference the tables directly.

 

[Kenln]

Here's the rub... I am not actually trying to update the PT query. I am trying to update the MSSQL table (directly) and set it to values from the PT query. The actualy query is in thread 'Why is this not updatable?'. If you wish I can copy it here as well.

 

[Banana]

Doesn't matter. If any query used in this query is nonupdate-able, so will be the final query. (Did you read the link to Allen Browne's page?)

If you're just using it as a criteria or something like that, use EXISTS clause.

 

[Kenln]

Yes I read it, but I wasn't sure if I understood entirely.

Well that stinks. Yet, oddly, simultaneously explains my problem and therefore my lack of solutions.

Sigh...

I could always write a dlookup, compare it to what is found and then use a straight forward insert statement.

Thanks,

 

[Banana]

Why not just use EXISTS? No need for dlookup and a separate INSERT statement.

Google it up for examples. This won't affect the updateability, IINM, because it's evaluated separately.

 

[Kenln]

I understand (I think) EXISTS, but not your applicataion of it.

 

[Kenln]

P.S. What about the original question regarding combo boxes. Should I rephrase this is a new thread?

 

[Banana]

Use it in the WHERE clause:

(off the top of head; rename & correct as necesary)

INSERT INTO tblTarget (column1="A", column2=1) WHERE primarykey EXISTS (SELECT primarykey FROM PassThroughQuery);

 

[Kenln]

You have:

INSERT INTO tblTarget (column1="A", column2=1)

Yet this value must come from the Pass-Through query.

i.e.

tblTarget.column1 = Passthroughquery.column1

 

[Banana]

I'm probably confused but I thought you wanted to use primary key between two tables as the criteria... However, if you just want to copy some values from one row in PT to a row in table, then there should be nothing stopping you from doing so.

INSERT INTO tblTarget (tblTarget.primarykey=PTQ.primarykey, tblTarget.column1=PTQ);

NOTE: In QBE view, there should be only table selected and that should be tblTarget. There is no need to have the PT show up at all, even though it is used to set new values for the new rows.

HTH.

 

[Kenln]

Here is what does not work.

UPDATE tbl_Vendor_Release_Info 
INNER JOIN CMSqry_Vendor_and_Contacts 
ON (tbl_Vendor_Release_Info.Vendor_No = CMSqry_Vendor_and_Contacts.VENDOR_NO) 
AND 
(tbl_Vendor_Release_Info.Div_No = CMSqry_Vendor_and_Contacts.DIV_NO)
AND
(tbl_Vendor_Release_Info.Co_No = CMSqry_Vendor_and_Contacts.CO_NO) 

SET tbl_Vendor_Release_Info.Vendor_Name = [CMSqry_Vendor_and_Contacts].[vendor_name]

 

[Kenln]

Remember, CMSqry_Vendor_and_Contacts is a PT query (READ ONLY)