SQL Server Performance Forum – Threads Archive

General Question PLease

Hey ,
I thought there was an Access form in this websit. I cant seem to find it. Did i make a mistake. I have an Access question to post if someone could help me that would be great.
Thank you
Mattie

Well, there’s one or two regulars here who know Access … as long as it’s not too broad a question …

Hey Adriaan,
Thank you for leting me post here.
I am attempting to create a function that when called will
look at a table (table 1) that is imported daily.
I need to take those records and insert them into a
permanent table (table 2). After this occurs I need to
update that table 2 with numbers that are held in a seperate
table (table 3) and once assigned (from table 3) are marked as
having been assigned (so that they are not assigned again).
The number of records that are being imported (table 1) will vary.
My question is how can I go about assigning these numbers to table
2 after the records have been inserted from table 1? What code
do I need to include in my function to carry this out?
What I am attempting to do is assign, as needed these ‘protected’
numbers that are held in table 3 marking them as having been assigned
once they have been used to update table 2. Thank you in advance for
any help you can give.
Mattie

Posting Access related question here is quite okay, I guess. However, since most folks are SQL guys, don’t expect too much. Having that said, I don’t understand your question. Can you give an example of what you are trying to do? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

In Access you should try to use the set-based approach whenever possible, same as in T-SQL. If you’re used to programming data manipulation iterating through recordsets in VBA, you might be surprised how little programming it takes if you use Jet-SQL. The Jet-SQL UPDATE syntax is a bit different from T-SQL, especially when joining multiple tables. With data merges, you do the update on matching records first, and next you insert new records (otherwise you will unnecessarily update the newly inserted records). — step 1 — UPDATE (Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.id = t2.id)
INNER JOIN Table3 AS t3 ON t2.fk = t3.rk
SET t1.colX = t3.colX, t1.colA = t2.colF — step 2 — INSERT INTO Table2 (……………..)
SELECT ………………….
FROM (Table1 AS t1 INNER JOIN Table3 AS t3 ON t2.fk = t3.rk)
LEFT JOIN Table2 AS t2 ON t1.id = t2.id
WHERE t2.id IS NULL

Thank you soo much Adrian , I am going to try that.
Frank I hope this helps.
Attached you will find the documentation regarding the three tables. The
explanation is this: The BoundForPolicy table represents the table with the incoming(imported)
data. PolicyNumber represents the table holding the reserved policy number
values needed to be assigned to the final Policy table. The BoundForPolicy
data is inserted into the Policy table and for that many new rows of data I
need to be able to assign reserved policy numbers to those rows based on the
fact that they are unassigned. Once they are assigned I will then update
the policy number table to reflect those numbers are no longer available.
OPPS sorry i dont know how to attach a pdf file. 🙁
Mattie

]]>