Query - Compare, Add, Change & Delete.

[Orbik]

Hi All

I am new to access and don’t really know what I am doing with the queries.
I thought I had sussed it after 3days of hardcore googleing but I havent :-(

I have attached my database.
Basically I would like all our stock records from our current system (Lakeview) in an Access database so we can search out products easier, Lakeview only has a field for a general description and I would like to search against more technical information which will be different for certain product types.

In the database the table (tbl_MST_LakviewStockList) which will have 10000+ records is what will be exported from our current system.
I have then created queries filtering out the information I would like from the list.
I turned the queries into tables and added extra fields for the purpose of searching and linking data sheets and drawings.

I will need to run the report from Lakeview on demand and update the access database by Adding new records, Changing existing, and Deleting old records.
If it is a change then I don’t want any of the additional related data removed.

So I changed a record, Added a record, and Deleted a record from the tbl_MST_LakeviewStockList

My problem is building a query that will Add, Change and Delete.

I thought I had sussed it following a tutorial and I have to a degree as the queries return the Added, Changed and Deleted records from the comparison I have done. These are called qryCompADD_..... and qryCompDEL_........
I then created queries based on those queries and turned them into Append and Delete queries.

When I run the queries I get errors :-(

Could someone kindly have a look and tell me where I am going wrong.

Thank you

Chris.

 

[plog]

I will need to run the report from Lakeview on demand and update the access database by Adding new records, Changing existing, and Deleting old records.

Why? You should just use tbl_MST_LakeviewStockList for the data you want from it and omit those fields from the individual tables you have for each part. That way there's no updating of existing tables--you would actually be using the correct data everytime you uploaded a new tbl_MST_LakeviewStockList file.

 

[Orbik]

Why? You should just use tbl_MST_LakeviewStockList for the data you want from it and omit those fields from the individual tables you have for each part. That way there's no updating of existing tables--you would actually be using the correct data everytime you uploaded a new tbl_MST_LakeviewStockList file.

[FONT=&quot]Why? I don't know it is the only method I could think of from what I have learnt so far .
Your answer sounds good but I don't understand what you mean sorry.

Could you give an explanation on how to do this please.

These will all eventually end up being displayed on a split form, one form for each category of which there will be more than 40. Each category will require around 5 fields that will differ from each other so in total the database will have 200+ additional fields required.[/FONT]

[FONT=&quot]I have made a sample form for the connectors in the DB to show what I mean and have attached it.[/FONT]

[FONT=&quot]Thank you for your time.[/FONT]

[FONT=&quot]Chris
[/FONT]

 

[Orbik]

Ok
After some searching and playing I think I have figured out what you meant Plog.
I have it all working and it is much easier now Thank you.

However I still need a report telling me the changes between the existing and new table which I cant work out. I only need to see the changes to the related categories.
I will have a play tomorrow as It is late now but will probably need some help as when I am puting bits in the critera of the query it is displaying everything and ignoring what I have in the criteria, I know it is me but tried a few things and cant figure it out

Anyway Thank you very much.