Updating Table with Foreign Key reference.

[benkingery]

I have a delimited file that I'm importing into a table (easy enough). This delimited file contains records that need to be split into two separate tables. 1) An Order Header table, and 2) an Order Detail table. Normally this would be easy enough, however I'll need to join these tables back up with a query, so I need the Order Detail table to contain a foreign reference to the Order Header table. Here is what the data looks like in its raw form.


IH|AF374556|2006-06-05|Bob Smith|3 Berk St||Boston|MA| 02199
ID|4056|4|55.50|Floor Lamp|Ground|Test Comment
ID|BC456|1|17.75|Hall Lamp|Ground|Test Comment 2

"IH" indicates that the record is a header record and "ID" indicates that it is a Detail record. Every "ID" that DIRECTLY follows an "IH" belongs to the "IH" above. That said, I want to use the second value in "IH" record (AF374556) as the foreign reference in the detail lines.

Does someone know how to systematically add the foreign value to the related lines? Either through query, or code? Obviously if my data set was this small, I'd add it manually, but I will likely be dealing with hundreds of "IH", "ID" records at a time and doing this on a daily basis, so that's kind of out of the question.

Thanks for your attention.

 

[georgedwilkinson]

Very dangerous thing you're doing here. In order to ensure this continues to work well into the future, you will need to put a key value on each row before you import it into an Access table (can be done in Access, as outlined in the next paragraph). The reason for that is that you cannot ensure that Access will put the lines in the same order they are in your source file.

You could open the file with VBA and iterate through each line, saving each IH record's key information to insert it in each ID record's foreign key column.

 

[benkingery]

Thanks for the reply. I also understand the risks, but my choices are limited. I'm using a temporary table to pull the records in, then make all modifications I need on that temporary table. Then I'm writing the header records to my header table and my detail records to my detail table through append queries. I'll then delete all records in the temporary table. Through this, I feel that I've minimized all the risk necessary, but I could be wrong. Can you comment on that? Shouldn't access always import the source file in the order that the data is in the source file as long as I use consistent import specs?

So, the solution you briefly mentioned (using VBA), did you mean doing something to the source file, or doing something once the records are in Access? I didn't follow exactly what you were telling me.

 

[benkingery]

Also, I need to add that the temporary table does have an identity specification field (autonumber) so all records are incrementally kept track of. Not sure if this helps or not.

 

[georgedwilkinson]

What I was saying is: do NOT import the file as it is into a table in Access. It will usually be right but will be wrong more times than you want. That is because neither Access nor any RDBMS guarantees that data will be held in storage in the order it is put into a table. Since your data only uses the relative position of records to indicate the parent/child relationship, this will be disastrous when it eventually fails.

What I was talking about using VBA for was reading the data from the file, massaging it (still not in any table in Access), and putting it into the correctly normalized tables with appropriate PK/FK pairs. You could also assign some keys in some other language or environment external to Access (for instance, VBA in Excel) and then use the Access import tools to put the modified data into a temporary table, etc.

What I am suggesting (and suggested earlier) is two different approaches, neither of which include importing the raw data file into an Access table (which will eventually break).

Is that a little more clear?

 

[gemma-the-husky]

i do stuff like this, but you have two options

either

a) easiest - make sure the file has some data that will relate the header to the corresponding details

b) if not - is it a text file or csv - the only safe way is to process it by reading it in a line at a time, and processing each line separately
then you know you are dealing with a header, and trhen its corresponding lines.

you cant load a table into access, and rely on it being in a given order.

furthermore - this data looks problematic anyeway, since the headers and details are not laid out in the same way.

 

[benkingery]

Thanks to both for the replies. Gemma, can you tell me what you mean that the data looks problematic? Also, I'm interested to know how you process each line individually, can you tell mea little bit more about that?

Georged,
I understand what you mean now about altering the file pre-access import. Lets say I build a safeguard that make sure access imports everything in the same order as the source file. Is there anything you can think of to help me populate the data as I've set forth if I can verify data is in there properly?

All, this file format sucks! I think we can see that. They're passing a normalized data set through a text file and calling it a flat file. This is the only option we have to integrate this particular customer i'm working with outside of EDI. We chose not to go with an EDI solution because of how time and capital intensive it becomes. This is the company's only alternative file processing system. Let me know your thoughts.

Thanks again.

 

[georgedwilkinson]

Open() the file in VBA.

Input# the first line of the file

Begin a Loop() until you've gotten the the end of file

Check the 2 Left() characters of the line

If this line is a "IH", parse the line and use SQL "insert" to write this line to your header table. Get the PK of the newly written record.
Otherwise, parse the line and use SQL "insert" to write this line to your detail table, using the PK from the previous iteration as your FK.

Input# the next line from the file

Go back to the top of the loop

Close() the file


It really doesn't get much simpler than this. Gemma and I have already mentioned, and I thought I explained: 1. ACCESS DOES NOT NECESSARILY STORE DATA IN THE ORDER YOU WROTE IT - THE ONLY SAFEGUARD TO FORCE ACCESS TO WRITE THINGS IN A WAY THAT YOU CAN GET IT BACK IS TO ENFORCE THE ORDER VIA PRIMARY AND FOREIGN KEYS 2. THE DATA IS MIXED FROM ONE LINE TO THE NEXT - THIS IS A PROBLEM

Don't be fooled by thinking that adding an auto-number to each record during a regular Access import will make sure the new ID field is in the correct order. I've seen this fail.

There's nothing really wrong with this file format. You just need to process each line in order and then go to the next project.

 

[benkingery]

Okay, so I solidified with our customer that we will only be receiving 1 file for every order. That means that our problem of duplication and ordering is gone. Within each file, there will only be ONE "IH" header record and then multiple possible "ID" detail records. That said, there is no risk of records getting out of order within the table.

So, that said, I have made it back to my original situation of how to do this. I have solved it by an append query and using a Dlookup for that field to append to the correct Detail table field.

Thanks all for your help along the way.