Append Query

[Cybersurfer]

I have a database with four tables created from an Excel Spreadsheet. I get
a new report weekly in Excel that I would like to import to the tables. How
do I set up an append query to bring this data in and update the four
tables?

Parent Child
Table Name Key Table Name Key
Element ID Capacity Element
ID
Site ID Element Site
ID
Market ID Site
Market ID

Any help you can give or reference to articles would be appreciated!!

Thanks,

Rich

"Superior Attitude, Superior State of Mind"

 

[Jeff Boyce]

An append query won't do the import. For that, you'd need either code or a
macro.

I couldn't tell what structure you have from how my email reader formatted
your message. Consider reposting, in a format something like:

tblOne
Field1 (Primary key)
Field2
...

tblTwo
Field1 (PK)
...

Are you saying that you are taking one Excel import dataset and parsing it
into 4 tables? Why? (I'm not being facetious ... it may be that this is
the normalization step, or it may be that your data design is more complex
than it needs to be.)

More specific descriptions lead to more specific suggestions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Cybersurfer]

Jeff,

When creating the table for the first time the table analyzer suggested
splitting. There is a lot of duplicate information so I figured it would
reduce the size and allowed it. Let me see if I can give you more
information.

Tables:

Market (Tbl)
ID (Primary Key) (Links to site.ID") (One to Many)
Marketing Market
State
Contact ID (Field Does not come from Excel)

Site (Tbl)
Site (From Excel)
ID (Primary Key) (Links to Element.ID) (One to Many)
Market ID
Address
City
State
Zip
Phone Number
Map
GPS
Lookup to Contact

Element (Tbl)
ID (Primary Key) (Links to Capacity.Element_ID) (One to Many)
Element (From Excel)
CMTSID

Capacity (Tbl)
ID (Primary Key)
Speed (From Excel)
98th w/ DOCSIS (From Excel)
Peak w/ DOCSIS (From Excel)
Subs (From Excel)
Online eMTAs (From Excel)
BW/Sub (From Excel)
CDV Subs % (From Excel)
CDV Subs Util % (From Excel)
CreateDate (Calculates Import Date)
Element_ID
LookupToContact
AssetID

The Spreadsheet fields are:
ISP Market
Market
Site
State
Element
Speed
98th w/ DOCSIS
Peak w/ DOCSIS
Subs
Online eMTAs
BW/Sub
CDV Subs %
CDV Subs Util %
Max CDV Subs @ 40%
Max CDV Subs @ 70%

The Excel Spreadsheet is a linked table in my MDB. I also link to a few
more tables that are not imported They are CMTS, Asset, and contact.
Hopefully I did not miss anything

Thanks,

Rich

 

[Jeff Boyce]

So, are you using multiple queries to "parse" out the data from the Excel
source into your "permanent" Access tables?

One way to run multiple queries is to create a macro and call each query in
succession. Another way is to do this same thing in code.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Cybersurfer]

Jeff,

I am just starting to learn code. My main goal is to ensure that the data
on one row of the spreadsheet says associated even if in different tables.

Thanks,

Rich

 

[Jeff Boyce]

Please don't post attachments in this newsgroup. Some of the 'group's
readers have to pay by the minute for their connection. Besides, would you
open an attachment from someone you don't know well?

If you are still willing to provide an example of your data, and/or a look
at your data structure, my earlier response offers a simple pattern.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor