Best way for updating a DB

[bb_]

I have a table that I made from a select query.
What is the best way to go about updating and adding to this table as the data is changed monthly?

*** for clarification
I have tables that are queryied in a select query, then this query was made into a table (so I can add it to sharepoint)
As the data changes and subsequently the query, how can I make it easy to Update and Add Records

Thank you!!

[Micron]

the data is changed monthly

How? by a query? A form? The updates/appends are on this data, or that place that you first got it from? If the latter, what is the nature of the source?

As the data changes and subsequently the query

This doesn't sound right if I interpret it correctly. Query design should not have to change because data changes.

If you have source data that may or may not be linked to the db (this is also unclear) you can update the target tables with an update query. If the source contains new records, you need an append query as well. But does the source contain records that are already in the target table and you don't want them to be added again?
I think you'll have to clarify much further as to what you're dealing with, what's going on and what you need.

[bb_]

Hi Micron, thanks for your response

I have 3 different tables from different sources. Names and employee numbers - phone numbers - account numbers

A select query to combine all of these into one (employee number is the FK)

I need these records on Sharepoint, so I made that select query into a table

Occasionally I plan to update those 3 tables as employees are added and their info changes.
The select query will still be as I want it... but for that table I created and then linked to sharepoint... how can i update it based on that query? Right now my plan is to just delete the table and then remake it... but I know it's not good design

[bb_]

nature of sources - excel sheets
yes - i don't want to append if the employee number is already in the table

[Micron]

I need these records on Sharepoint, so I made that select query into a table

I have practically zero experience with SharePoint, so excuse me if I am a bit thick about it. Not sure what you mean. If you're copying records from a select query and dumping that into a table, why not just turn the select into an Append query?

how can i update it based on that query

The usual way is to update or append to the table by entering/editing data into a form when you have based the form on the table. If you can't or shouldn't because the data has been entered somewhere (say by HR and you want to eliminate potential input errors on your part) and you have access to it, you can link to this source (even if its a workbook - better if its name never changes). Then to add records, you'd join your table and the source table in an append query. The join(s) should be between one or more PK fields that are in both tables. In the Update To row of the query design grid, you put NameOfSource.NameOfField (e.g. tblEmployee.EmplID) in the EmplID field of the query. If you switch to design view it might make more sense to read the resulting sql than to visualize it in query design grid.

To avoid duplicating records, the target table must either have a PK field or one or more fields that have been indexed and the index property for dupes is set to No. The process may raise warnings about not being allowed to add dupes and at first, you would have to OK the messages. I'd be getting way ahead of you to deal with that now.
Hope that helps you get started. Some of what you have is still not clear, such as if the source is a workbook, is it's name static? If not, can you repeat the Get Data process (from External Data ribbon tab) for each name change? Would that mean another table with a different name? Or would you overwrite the linked table. But you're right - continually overwriting the same table can lead to bloat and corruption.

[bb_]

Thank you for your help Micron. now my issue is I want to easily be able to update (where employee i'd = employee id).

[Micron]

Not enough info. Update what table from what table? Or is it to update a table with a particular value (& where would that value come from?).

[bb_]

update from tables to the new table.

[bb_]

sorry. it says novice under my name for a reason ��

[Micron]

Understand the novice thing, no problem but...
I'm not getting through (I asked "what" table for each side of this). I also said in post #5 that a form is often used for this type of thing. At this point, I don't know if a form is involved or where the update is coming (table name) or where it's going to (table name) nor the field names involved.
Provide specific examples, including values to use for the updates (creating little tables of data in Excel and dumping into your post works pretty good). Or post a zipped copy of a db with whatever's needed to complete this (form? target table / source table) etc.
Note: if your db uses design options not compatible with 2007, I won't be able to open it. In that case, workbook attachment here with data I can use might work, but a db would be worth a try first. Then again, we might get somebody to assist on that side of things.

Or you could Google MS Access Update query and see if that helps. I think you need an example that involves joined tables.

[ArviLaanemets]

In case the excel table has a single column which serves as unique identifier (let it be UID), and it exists as field in your access table too, then to add new records
INSERT INTO AccessTable (UID, ...) SELECT UID, .. FROM ExcelTable WHERE UID NOT IN (SELECT UID FROM AccessTable)

To update existing records
UPDATE AccessTable a INNER JOIN ExcelTable b ON a.UID = b.UID
SET a.Field1 = b.Field1, ...

In case to get unique id you have to combine several columns, in update query you simply add join conditions for those fields.
In append query, probably the best way is to join both tables with LEFT OUTER join - the records missing from Access table will have Nulls in condition fields:
INSERT INTO AccessTable (Field1, Field2, ...) SELECT a.Field1, a.Field2, ... FROM ExcelTable a LEFT OUTER JOIN AccessTable b ON b.Field1 = a.Field1 AND b.Field2 = a.Field2 WHERE b.Field1 Is Null OR b.Field2 Is Null

In case you have SQL Server available (Not Express where you can't create jobs), you can consider to keep mirrors of those excel files in a SQL database instead. Then you can create stored procedure(s) to update mirrors of your Excel tables in SQL Database, and define a job, wich routinely (p.e. in every night) updates them. You can link those tables, or a SQL Database View with all 3 tables joined, into your Access database.