Combining a data from a linked table and a regular table in one fo

[Mishanya]

I have a table linked with Excel, wich is updated from time to time by
adding/deleting a new record of 4 fields (the update is made in Excel by
saving a table from the web).
I have a regular table with 4 more fields (1 of wich has the same name as
the 1st field from the linked field and is a primary key, the rest 3 are
lists of values), wich are empty and should be filled in accord with the data
from the linked table.
The tables have to be combined in a form (building an 7-field records,
ommiting only the repeating field), so that the empty fields from the regular
table can be updated in the form manually.
I can not relate the tables, cus the linked table can not have a key - so
the simple form with 7 fields from the 2 tables can not retrieve an existing
data from the linked table and relate it to the empty fields from the regular
table.
Creating an Append query with all the fields from both the tabes did not
solve the problem - the form based on it does retrive the existing data, but
still does not allow to choose from the lists of the empty fields.
I don't want to update the regular table by adding a new record whenever it
has to be done, but to do it in the form - I mean, every time a new record is
added to the linked table, a new record appears in the form and it has 4
already filled fields and another 3 waiting for chosing from the lists (wich,
in turn, update the regular table).
How can I do it (directly by a form or by building a query first)?

 

[bhicks11 via AccessMonster.com]

Is your problem what to relate the two on? Otherwise:

Create a table with the primary key that you need from the Excel worksheet.
Make an append query that appends the excel data to the table you created
with the key. Use that table related to your "regular" table to do your
update. If needed, run a delete query first on the table receiving the Excel
data each time you run your update process.

Bonnie
http://www.dataplus-svc.com

 

[Mishanya]

First of all - thanks.
Done as U'd adviced.
1) created a similar-stuctured-to-the-"linked"-table "new" (unlinked) table
2) created an Append Query wich copies data from the "linked" to the "new"
3) created an Append Query wich adds the field from the "linked", used as
the primary key in the "new", to the "regular" table
4) created a Form wich combine data from both the "new" and the "regular"
thru the primary key (one-to-one relation)
5) created a Delete Query wich deletes all the data from the "new"

Now, in order to update the DB I have to:
1) update the Excel file (the "linked" table updates automatically)
2) ececute the Delete Q on the "new"table"
3) execute the Append Q on the "new" table
4) execute the Append Q on the "regular" table (don't have to clean it
first, cus the Form takes only the updated records from the "new" table; the
outdated manual records in the "regular" table can be used in the future, if
the their relevant fields (primary key) appear again in the "linked" table,

Indeed, it works. But from the end-user point of view the whole process
seems cubersome - 3 query executions instead of just pressing the form button.

Can U suggest any optimization (shortcutting or macros wich executes all the
queries and the form in 1 press), or the linked data does not allow too much
options?

 

[bhicks11 via AccessMonster.com]

Why not make a button that runs the queries in the required order? You can
either run a macro or put the code in VBA.

doCmd.OpenQuery "DELETEWHATEVER"
DoCmd.OpenQuery "APPENDWhatever"

etc.

Should be seemless to the user.

Bonnie
http://www.dataplus-svc.com

First of all - thanks.
Done as U'd adviced.
1) created a similar-stuctured-to-the-"linked"-table "new" (unlinked) table
2) created an Append Query wich copies data from the "linked" to the "new"
3) created an Append Query wich adds the field from the "linked", used as
the primary key in the "new", to the "regular" table
4) created a Form wich combine data from both the "new" and the "regular"
thru the primary key (one-to-one relation)
5) created a Delete Query wich deletes all the data from the "new"

Now, in order to update the DB I have to:
1) update the Excel file (the "linked" table updates automatically)
2) ececute the Delete Q on the "new"table"
3) execute the Append Q on the "new" table
4) execute the Append Q on the "regular" table (don't have to clean it
first, cus the Form takes only the updated records from the "new" table; the
outdated manual records in the "regular" table can be used in the future, if
the their relevant fields (primary key) appear again in the "linked" table,

Indeed, it works. But from the end-user point of view the whole process
seems cubersome - 3 query executions instead of just pressing the form button.

Can U suggest any optimization (shortcutting or macros wich executes all the
queries and the form in 1 press), or the linked data does not allow too much
options?

Is your problem what to relate the two on? Otherwise:

[quoted text clipped - 30 lines]

 

[Mishanya]

OK, I'll take this. Although, this way dealing with linking seems to be very
complicated.
I am building a DB in wich some stocks indicators will be brought from the
web (quotes etc.) thru Excel table, and some will be determinated by user for
his persanal use. The Form will enable him to add such an indicators from the
listfields. But then the web-data should be updated (new records in
portfolio, new quotes, deleting a records) but the manual data for the
relevant records should stay intact.

Anyway, thanks a lot, Bonnie! Spasibo (in Russian)!

Why not make a button that runs the queries in the required order? You can
either run a macro or put the code in VBA.

doCmd.OpenQuery "DELETEWHATEVER"
DoCmd.OpenQuery "APPENDWhatever"

etc.

Should be seemless to the user.

Bonnie
http://www.dataplus-svc.com

First of all - thanks.
Done as U'd adviced.
1) created a similar-stuctured-to-the-"linked"-table "new" (unlinked) table
2) created an Append Query wich copies data from the "linked" to the "new"
3) created an Append Query wich adds the field from the "linked", used as
the primary key in the "new", to the "regular" table
4) created a Form wich combine data from both the "new" and the "regular"
thru the primary key (one-to-one relation)
5) created a Delete Query wich deletes all the data from the "new"

Now, in order to update the DB I have to:
1) update the Excel file (the "linked" table updates automatically)
2) ececute the Delete Q on the "new"table"
3) execute the Append Q on the "new" table
4) execute the Append Q on the "regular" table (don't have to clean it
first, cus the Form takes only the updated records from the "new" table; the
outdated manual records in the "regular" table can be used in the future, if
the their relevant fields (primary key) appear again in the "linked" table,

Indeed, it works. But from the end-user point of view the whole process
seems cubersome - 3 query executions instead of just pressing the form button.

Can U suggest any optimization (shortcutting or macros wich executes all the
queries and the form in 1 press), or the linked data does not allow too much
options?

Is your problem what to relate the two on? Otherwise:

[quoted text clipped - 30 lines]

in turn, update the regular table).
How can I do it (directly by a form or by building a query first)?

 

[bhicks11 via AccessMonster.com]

Your welcome Spasibo - you are welcome to post again for a sleeker suggestion
if you want. There is always more than one way to skin a cat.

Bonnie
http://www.dataplus-svc.com

OK, I'll take this. Although, this way dealing with linking seems to be very
complicated.
I am building a DB in wich some stocks indicators will be brought from the
web (quotes etc.) thru Excel table, and some will be determinated by user for
his persanal use. The Form will enable him to add such an indicators from the
listfields. But then the web-data should be updated (new records in
portfolio, new quotes, deleting a records) but the manual data for the
relevant records should stay intact.

Anyway, thanks a lot, Bonnie! Spasibo (in Russian)!

Why not make a button that runs the queries in the required order? You can
either run a macro or put the code in VBA.

[quoted text clipped - 40 lines]