update query question

[blusk06]

I am new to this forum, but I am somewhat experienced in Access. I have been building a database in Access 2008 which basically takes parameters of devices (computer hardware) out of tables and builds all possible configurations based on the criterion I set based off those device parameters.



I have 4 different append queries that need to stay separate based on the structure of the data and how I am doing my queries.

What I want is to be able to add another computer platform, or hardware device, into my tables, be able to run my 4 queries, and have them add to my big configurations table only the query results that DONT have certain fields matching (6 to be exact are what I need to define a unique config). Obviously the append queries just make the table huge and have a ton of duplicate values. I want the affect of the append query without the duplicates.

I realize this is a somewhat simple concept, but what would be the best/easiest way to implement this? I was thinking I could have 2 tables, one that has all my configs, and one that contains the output of the append queries, then have another query that left joins the master table with the one that has the output of the append queries. But that would make the table that has the output of the append queries just get HUGE over time and fill with duplicates....

Any suggestions?

[narayanis]

I'm still new to access in particular, but would it be possible to create a composite primary key of the six fields to prevent duplicate entry?

[blusk06]

how do you go about creating that composite primary key?

[narayanis]

in your table design, you can Ctrl+click multiple fields and then click the Primary Key icon on the toolbar. I'm sure there are other ways to handle what you want to do that will give better performance, but this is the only way I can think of to do it in access. If it were in SQL then I would use a Stored Procedure to create temp tables, run your four queries and store the results in the temp tables, and then use SQL logic to insert rows from the temp table into the live configuration table based on your business rules. I don't think Access can do sprocs, however.