[url="http://www.lexusownersclub.com/gallery/Misc/help?full=1"]Click here for image[/url]



Ok there are two files:
transaction file
and new car stock file
each file contains a field name called CarID

what i want the delete query to do is when the tranaction file-Car ID equals the New Car Stock-CarID the record containing the carID is deleted in the New Car Stock File

please help and i hope this make sense

thanks
Paul

I could probably knock this up with a cuople of minutes but here's a short hand version of what the SQL should look like:

Delete from New Car Stock
where transaction.car id = new car stock.car id.

Of course MS Access doesn't make it that easy and there will be square brackets everywhere...

I'm having just that problem with another query trying to get sql into access........................hopefull I'll crack it today

the best way to do this is a trigger.

but I don't think access supports triggers.


considering the questions you have posted about this Paul - I would seriously recommending using a better DBMS such as SQL Server or Oracle (oracle used to offer v8 for free download - not sure about sql).


otherwise, in access, I suspect you'll need some kind of custom control that deletes from the tables you need. You just need to alter your form then to call this control, and pass CarID as a parameter.

thanks for the repies.

very very novice question but what is SQL server ??
it sounds alot easier than this m/access stuff.

[quote name='PI100' date='Mar 15 2004, 04:31 PM']very very novice question but what is SQL server ??[/quote]
[url="http://www.microsoft.com/sql/default.asp"]http://www.microsoft.com/sql/default.asp[/url]

[quote]it sounds alot easier than this m/access stuff.[/quote]

not necessarily easier.

but significantly better than Access - which is not much use for anything other than pretty mickey mouse usage.


If you want a better dbms than access, but don't have the money for SQL Server- then look into the freebie version of Oracle (although I find that its java applications for management run like crap - even on a decent pc).

Otherwise PostgreSQL is a good open source alternative.

u cud always get mysql database its free 2 use.

[quote name='Andy Freeman' date='Mar 15 2004, 06:20 PM']u cud always get mysql database its free 2 use.[/quote]

so is PostgreSQL

MySQL is pretty cool.

LOC runs on a MySQL db


that said -I would not base any sort of enterprise level application on MySQL (then again - nor would I base it on Access! 8) )

it does not have the functionality such an application requires ([b]views[/b] will not be implemented 'till version 5.1) nor would it scale to enterprise level volumes.



If Paul needs a better, free, DBMS - then I would say he should look at postgresql - it has a lot of the functionality any decent dbms should have (views, triggers and stored procedures would help from what I can tell).


but this is going

Paul - no-one seems to be arguing with my idea of writing this delete processing yourself, within the scripting language your are using (ASP, php, VBA etc). IMHO, something like a trigger would be exactly what the doctor ordered, but if your DBMS of choice does not support that, then you need to look at implementing this fucntionality yourself