SQL noob - Inventory queries, merging tables on criteria. 1

[bunglefoot]

Hey, posted a while back on this subject and got some good responses but nothing I could get working in the desired manner. I have more information now.

I have a table with four fields coming in from a device (called deviceTable). The table succesfully enters MS Access with no difficulties.

It has four fields, field1 is text, field2 is text from a drop-down control, field3 is a date, and field4 is text from another drop-down control. There is a similar table in existance on the PC (pcTable) with all of these fields as well as a numerical field, field5. Records in these tables and between the tables are considered identical IFF both field1 AND field2 are identical.

What I need to do, in order:

1: Take deviceTable, and merge it with pcTable such that any record in deviceTable that does not exist in pcTable is created with field5 initialized to one. If the record in deviceTable matches a record on the PC (field1 and field2 match a counterpart record's field1 and field2 on pcTable) then the pcTable's field5 is incremented by one.

2: Print out pcTable, it doesn't really have to be pretty but it should be sorted by field4.

3: Remove any and all records from pcTable that are present in pcTable but not in deviceTable.



This is just using the SQL view of the query designer in MS Access 2002. Ideally, all three steps would occur at once with minimal input from the user.

Thanks for your help!

 

[SQLSister]

This is not an Access forum. Are you using SQL Server as the backend?

If you are using Access, I don;t believe you can do all three steps in one query using Access designer, but you can write code that will perform all three steps as individual queries as part of the form (I'd probably attach the code to the click event of a button.

If you have a SQL Serve backend, then I would put these into a stored procedure and run that from the form. I would also put in tranasaction processing to mke sure that all actions happen or the entire job fails.

What you want are variations of the following queries (this is T-SQL, you may need to adjust for Access)

Insert query for the records which exist in one table but not the the other. Use a left join. Something like:

Insert into table1
Select field1, field2, field3, 1 from table2 left join table1 on table2.id = table1.ID where table1.id is null

An update query to update the rows that already exist:

Update table1
Set field5 = field5+1
from table1, table2
Where table1.id = table2.id

A delete query to delete the records you don't want:

 delete from table1
left join table2 
   ON table1.id = table2.title_id
WHERE table2.id is null

A select query to grab the rows you want to display:

Select field1, field2, field4 from table1 order by field4

You can, of course, join on more than one field, it sounds like you will have to do that.

Questions about posting. See faq183-874

 

[SQLBill]

If you are using MS Access and not SQL Server, you are better off posting in the appropriate (Access) forum.

-SQLBill

Posting advice: FAQ481-4875

SELECT 'forum' CASE [SQL TYPE]
WHEN 'MySQL' THEN 'forum436'
WHEN 'Access' THEN 'forum700', 'forum701', 'forum702', 'forum703', 'forum704', 'forum181'
WHEN 'ORACLE' THEN 'forum185' 'forum186', 'forum759'

 

[ESquared]

Cute, SQLBill. I wish that post was creatable with the click of a button!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLBill]

ESquared,

I keep it in a Notepad file, along with other 'comments' like the one about BOL. When I need one, I just copy and paste.

-SQLBill

 

[SQLSister]

Bill - syntax error - you know that CASE won't work without an END.

Questions about posting. See faq183-874

 

[ESquared]

LOL

-------------------------------------
A sacrifice is harder when no one knows you've made it.