Re: Combining a data from a linked table and a regular table in on

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

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" wrote:

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 wrote:
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)?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200809/1


.

Relevant Pages

  • Re: Deleting table in another database
    ... >manipulate/create a primary key then in a table in ... >records if I use a Make Table query, ... >error when I use an Append query. ... >>Mark wrote: ...
    (microsoft.public.access.modulesdaovba)
  • RE: Append Query
    ... All fields are duplicated in Table1. ... I thought about using a primary key, ... I am using an append query to appends data in a table. ...
    (microsoft.public.access.queries)
  • Re: Running into error while writing Append Query
    ... AutoNumber ID as the Primary Key. ... When I run it as a Select Query, ... Check your data -- if you change that Append query to a Select query, ...
    (microsoft.public.access.queries)
  • Re: duplicates
    ... UserName <Text, Primary Key> ... Manually fill the Steps table with your fieldnames - Edit, Query, Add, Delete, ... Use the Relationships window to define a relationship from Users to StepsDone, ... Base a new Append query on your UNION query and append the data into the new ...
    (microsoft.public.access.queries)
  • Re: Cannot insert record
    ... I can't imagine ever using a query with all three ... The first table has a primary key WO wich is an autonumber. ...
    (microsoft.public.access.queries)