Importing Data, Creating New Tables with Update Querey

[wjpennington]

I'm sorry for these stupid basic questions. I've been told to start using access to create various reports, got some rush two day seminars, and now I'm supposedly the local access expert.

I'm lost and feeling utterly helpless , and just want to go back and say "this can't be done". but, I'm going to give it one last try.

We have a very crappy case management system we have to use. Its inflexible, and doesn't do much of what we need. therefore, we are exporting data from it, in excel, to use in Access to create the reports we need.

So, weekly, I have to download the data from our program. Each case has a unique case number, and lots of related fields.



I have several additional fields to add to each record needed for the type of reports we want to do. (Special Case Types, Status summary, progression of each case, etc).

However, updating this information is pointless. Because, each week, when I import the update data from our case management program to the access table, all the additional fields I've entered will be wiped out.

I could create an additional separate table with those fields, using the case number as the primary key to have the tables relate to each other, but then I'll have to manually enter a case number every time a new case is created so the fields I'm entering data for can be linked up with all of the data in the imported table. Which duplicates all the work I'm doing with the case management system. . I cant even just import the new case numbers, as that will wipe out any data in that table.

Is there any way to import information without destroying updated fields? Can I designated certain fields as locked and not to be overwritten in the import? These fields wont exist in the source file anyway, so why are they being removed by the import?

[isladogs]

There's a few things that don't make sense here.
You should be importing your data to an existing Access table designed for this purpose and which has all the fields you require.
Each week append the new records from your external source to this table.
Then populate the additional fields.
The following week, append the latest records but don't delete the previous ones

Do NOT overwrite the table each week

[wjpennington]

I have to overwrite it. its not just new records being added, but fields in the old records may change from day to day. If I only add new records, I miss those updated values.

example

The table I import has

Primary Key:Case #; Field A ; Field B ; Field C

Not only are new records added, but the contents of fields A,B, and C change. I just cant append new records.

I'm importing this, and want to add fields X, Y, and Z to the table. But if I do that, everything gets overwritten when I import the new files, and fields X, Y and Z are lost.


The only thing I can think of is to create a separate table with X,Y, and Z and relate them using the case number.

Ok, had a thought. I import a second excel file, consisting of the only the case number, and only append new records to the second table with fields X, Y, and Z. That preserves the existing fields X, Y, and Z, and everything can be related to the other imported table by case number.

I just have to do two separate imports. Does that sound correct?

[Gicu]

Unless they use the "crappy case management system" as their place of adding and updating the data and the new Access app for reporting only in which case they need to overwrite the data each time.

If case number is unique then create a separate table that has that and your additional fields. Build a main form based on your imported data table (the main info coming from the case management system) and add a subform to it to show the related "additional fields" from the Access table, linking it using the Case Number field. This way you can populate those fields easily for the new cases as the case number field will be auto-populated by Access (because of your linking).

Cheers,
Vlad

[Gicu]

You are over-complicating this. Just create a new table in Access to hold your CaseNumber,x,y,z fields. Those will be managed (added/updated) only in Access anyway, so build a simple interface as I suggested and you won't have to worry about manually adding the case number.

Cheers,
Vlad

[wjpennington]

Ok, thank you for the help.I'll just do it manually for a bit, because I don't have the hang of forms and subforms, but I'll keep reading until I figure those out and do it that way. My sum total experience of six hours of instruction after decades of desperately avoiding Access isn't much to fall back on, but I'll get there eventually.

[Gicu]

It is not that difficult as you may think. Access has great wizards that do most of the work for you. Go to the Create Tab on the Ribbon, click on the Form Wizard and follow the steps (select your main imported table, etc.). Once you have the form switch to design mode and click on the "Subform" control on the ribbon, select an open area on the form (probably towards the bottom) and Access will prompt you again to select your table (now you select the one with the additional fields), select the form view (probably best would be a datasheet form). Give it a try and play with it and you'll soon fall in love with Access like most people on these boards.

Cheers,
Vlad

[wjpennington]

::edit:: found the hangup. Ok, forms and subforms in pace. various tables all playing nice with each other, updating as they should. thank you again.

[Gicu]

Glad to hear! Would you please mark the thread as Solved?

Cheers,
Vlad