Re: How to reorganize pieces after import from large database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Okay....that helps clarify things for me a lot. I think the 'Unique Values
Property' bit is especially helpful. I do have multiple input
formats....several actually...so your advice on that is much
appreciated...well all of it is. I'll get back to work on this now but I have
a feeling I'll be back with more questions.
Thanks again for your help!
Kristine

"Edward Reid" wrote:

On Wed, 28 Jun 2006 11:55:02 -0700, smilee8_28 wrote:
I'm stuck on step 6. Can you explain what it does? Is this used to populate
the Item tables?

Yes. Ah, but I'm glad you asked, because I missed one important thing. In
the step 6 queries, go to the query's property sheet, and set the Unique
Values property to Yes. The query doesn't make much sense without this --
and in fact would cause a run-time error when there's more than one
instance of a new value in one input file.

For the most part, the Item tables won't change so I'm a
little confused.

If they really don't change, then you can populate them some other way and
skip this step. In my case, I do get new values and I do want to
automatically add them. I should have made this clear.

However, as written, if your input data has items which are not in the item
table, then the append to the transaction table will silently drop those
records. Most likely this is a Bad Thing. ;-) So you need to either run the
query to append any new values to the item tables, or else create a query
which will tell/warn you when new values are present so that you can handle
them in whatever way is appropriate in your context. No matter if the
people sending the file tell you there will never be new values: trust but
verify! If adding the new values is the right thing to do, then just let
the append query run -- even if it usually doesn't update anything, it'll
be quick and won't do any harm.

Why the is null criteria?

This is to select input records for which there is no corresponding record
in the item table. The left join gives you a record for each value on the
input whether it's in the item table or not; the "is null" selects those
records from the left join which had no record in the item table. These of
course are the values which need to be added to the item table. (Or
reported as errors, if adding them automatically is not appropriate.)

And....how does this work when I
have mulitple input tables?

Do you mean multiple input files in the same format, that is, importing
multiple files at the same time? Or multiple input formats, requiring
multiple formats of input table, which is what I mentioned?

If you need to import multiple files at the same time (all in the same
format), then you just repeat the first part of step 9 until you have
appended all the input files to the input table, then proceed with the
remaining steps. (In my case, where I've automated it, I have a VB
procedure which finds all the files in a folder, imports them to the input
table, and renames them into another directory. Actually I've gone even one
step farther -- when I import a file, I save its name in a table. The file
names contain the date, so then I check new file names against this table
and reject them if I previously imported them.)

If you have multiple different formats, then you'll need to repeat steps 4
through 8 for each format -- that is, the input table and update queries
will be different for the different input formats. Then you'll have to run
step 9 for each file format -- though you could start with all the imports
and then do the remaining steps for each format. An alternative would be to
have a second input table, but as soon as you import into it, move the
records to the first table, reassigning fields as needed. Note that if the
only difference is the order of the fields, and the field names are in the
first record of the input file, and you can arrange for your input table to
have the same names, you may be able to get away with only one table and
even only one import procedure by using the "first record contains field
names" option of the import process.

Edward
--
Art Works by Melynda Reid: http://paleo.org


.

Relevant Pages

  • Re: How to reorganize pieces after import from large database
    ... Do you mean multiple input files in the same format, that is, importing ... procedure which finds all the files in a folder, imports them to the input ... first record of the input file, and you can arrange for your input table to ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Text Import
    ... You have multiple lines which you wish to treat as one lines. ... the new super line is of a fixed format, ... I can't work out your format due to the line wraps in the message, ... by fooling it to what you want with a dummy file. ...
    (microsoft.public.sqlserver.dts)
  • Re: Directory form is now open!
    ... It's ok to peek but I really need to update ALL these pages. ... Please do not enter multiple addresses or screen names. ... WebTV user. ... have some unique format issues that we need to address. ...
    (rec.crafts.textiles.quilting)
  • Re: MS word suggest: Doc linking
    ... >> I've done when writing papers within LaTeX. ... >> multiple images to print with a different print processor than Word ... >> a particular form which has been composed in Word Format and I have ... >> accesses them in any order, forcing sequential access to a sequence ...
    (microsoft.public.word.drawing.graphics)
  • Re: MS word suggest: Doc linking
    ... >> I've done when writing papers within LaTeX. ... >> multiple images to print with a different print processor than Word ... >> a particular form which has been composed in Word Format and I have ... >> accesses them in any order, forcing sequential access to a sequence ...
    (microsoft.public.word.docmanagement)