How to add a memo field for query to non-writeable db? 1

[kellstee]

I wrote a query to show orders and their due dates using a ODBC link to our orders database (the db is sql server if it matters). We're not able to write back to the database (or just really don't want; we would hate to screw something up).

One guy who handles the orders would like to have an extra field in the returned results called "Ralph's Notes" where he can type stuff into it and the information would be saved.

Is there an easy way to do this? I was thinking instead of launching a select query, teams could launch a macro that would save the results of the query to a new table and then it would be easy for Ralph to enter his notes. However, the results change every hour, so then don't I have to consider updates, inserts, and deletes to the recordset?

I should mention that I'm pretty good at writing select queries and can do an insert or update query if I had to, but I don't write them a lot and I've never really had a need for a form or report. I just write a lot of select queries.

I wrote the query in question in Access and it's saved to a shared drive, so multiple teams can see what orders are due for the day. We would like to be able to all see Ralph's notes.

Any help provided would be most appreciated.

Kelly

 

[PHV]

Why not using a linked table with a 1:1 relationship to a local Ralph's note table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kellstee]

Well, my query returns order data like this. . .
OrderID 1, 2, 3, 4

Tomorrow since there may be some new orders and some cancelled ones, it will be
OrderID 1, 2, 3, 5, 7, 8, 10

So wouldn't I need to first create [Ralph's Notes] table with all of my orderid's in it and a blank "Notes" field that he could type in?

So I could create a table with orderid 1, 2, 3, 4 today, but tomorrow the table would have to be updated with records for 7, 8, 10?

Everyday it seems I would need to add the new orderid's to [Ralph's Notes]? Does that sound right? Since I would join on orderid?

If so, what's the best way to make sure the update statement gets run?

Kelly

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kellstee]

Thanks! I've known about joins for a long time, however, never really understood when I would need one and now I totally get it! Thanks for being patient with me!

Kelly

 

[kellstee]

PHV,

Just one more thing. . .my query is now taking quite awhile to run. This is the query. . .any ideas on how to speed it up?

SELECT OrderNumber,
o.ShipFirstName,
o.ShipLastName,
o.Status,
b.Notes AS [Progress Notes]
FROM [Bob's Notes] AS b RIGHT JOIN dbrders AS o ON b.OrderID=rderID
WHERE Exists (Select 1 From dbrderItems oi Where oi.OrderID = rderID and (Name Like '*red*' or Name Like '*blue*' or Name Like '*green*'))
and StoreID = 1
and o.Status<>"Shipped"
and o.Status<>"SP"
and o.Status<>"Claims";

I think my "exists" statement is really slowing things down. I'm not sure of another way to write it though.

Kelly