Importing data into custom Access form

[Recumbamom]

My work uses Microsoft Access for registering people for an event. We want
to start online registration in addition to paper registrations. The online
registrations are going to be downloaded in to Microsoft Excel and from there
I want to import the data into our registration program in Microsoft Access
2003. Since we have a custom form, how does the data get imported into the
form? I want it to merge seamlessly with the paper registrations/program.

 

[Ken Sheridan]

A form is only a way of interfacing with the data in the underlying table(s),
so whether you import into Excel or directly into Access the important thing
is that the imported data is such that it can easily be appended into your
registration database's tables.

Excel data is not structured in the same way as Access tables, or at least
in the same way as those in a well designed Access application. The latter
will have a set of related tables, each one representing a distinct 'entity
type', whereas an Excel workbook is more akin to one big table, which will
inevitably contain a lot of 'redundancy'. You can link to the Excel workbook
from your Access database via the File | Get External Data | Link Tables menu
item on the main database menu bar, but you'll almost certainly need to then
execute a series of queries to append the data correctly into your Access
tables. This is not a trivial task as you'll need to cater for data which
may already exist as rows in the current tables as well as data which will
need to be inserted as new rows, e.g. somebody might register whose address
is in a city which already exists in the database, in which case you'd need
to reference the existing row in a Cities table. On the other hand if
someone from a city not already represented register than you'd have to
insert a new row into the Cities table with not only the name of the City but
the state in which its located.

Ironically the task is easier if the database is poorly designed and
contains redundancies, e.g. if rather than having separate Cities and States
tables these were recorded simply as values in columns in a table of
addresses. You'll find an example of a table with such redundancies in the
sample Northwind database in fact in the case of its Customers table. You'll
see that this has City, Region and Country columns so we are told numerous
times that São Paulo is in SP region (as is Resende) and that SP region is in
Brazil. Not only does this require repetitive data entry, but more
importantly it opens up the risk of inconsistent data, e.g. it would be
perfectly possible to put São Paulo in California in one row and California
in Ireland! Proper normalization as I described above would prevent this as
the fact that São Paulo is in SP region would be stored only once in the
database as would the fact that SP region is in Brazil and California is in
the USA.

Apologies in advance if you respond to this and I don't get back to you, but
after tomorrow I'll be away incommunicado for a while.

Ken Sheridan
Stafford, England

 

[Dominic Vella]

Personnally, I think you'd be better off putting the database into the
database straight up. Use a USB Thumb drive to cart it around if you like
(Keep backing up).

I gather you're suggesting Excel because the other computer does not have
MS-Access. If that's the case you can always check out the Microsoft website
and look for the FREE Runtime version of access.

If all else fails you you decide to use excel, then I'd suggest creating
your database first, then export it to a new Excel file so that you can see
the preferred Access layout and use that version for your dataentry. It
will make Importing the data back a lot easier.


Dom

 

[Recumbamom]

My work uses Microsoft Access for registering people for an event. We want
to start online registration in addition to paper registrations. The online
registrations are going to be downloaded in to Microsoft Excel and from there
I want to import the data into our registration program in Microsoft Access
2003. Since we have a custom form, how does the data get imported into the
form? I want it to merge seamlessly with the paper registrations/program.

To clarify, another company will run the online registration and send us the
data in Excel, we don't have an option on that. Do we need a programmer to
set up the append queries? If so, can we handle the flow of data after it's
set up?

 

[John W. Vinson]

To clarify, another company will run the online registration and send us the
data in Excel, we don't have an option on that. Do we need a programmer to
set up the append queries?
No.

If so, can we handle the flow of data after it's
set up?

Yes.

Open your Access database. Select File... Get External Data... Link from the
menu.

Choose Excel from the "files of type" list.

Select the spreadsheet that you get.

You now have the linked table. Base an append query on that linked table; save
the query.

If the excel file has the same name every time - or if you copy it over the
file that you linked in the previous steps - you're done. Just run the query
every time you get a new spreadsheet.

 

[John W. Vinson]

If you need help setting up the append queries, I can help you for a very
modest fee.

And lots of the rest of us will gladly help for free.

 

[Dominic Vella]

What I would do is create a new table solely for the purpose of updating
your data, so for example, if updating a customer table (tblCustomer) I'd
create a new table called "tupdCustomer" with fields sorted similar to the
spreadsheet layout. I'd also include an extra Yes/No field called
"customer_updated".

Then I'd copy from the excel data area. In this way it doesn't matter what
extra look pretty junk is on the spreadsheet. Then paste the data into the
Update table.

When you create the Update Query, you can link the data together, do the
table updating and also tag the "customer_updated" field in your update
table.

After you run the update query you'll be able to delete all the records that
have the customer_updated tagged and review which records didn't get tagged.

If this sounds too difficult to understand, let me know and I'll give you a
more detailed outline.

cheers


Dom
www.effectivedata.com.au