question regarding syntax etc

[NSmoller]

SELECT l.MD, l.Patient, l.SSN, l.Drug, l.LASTFILL
FROM Locallist6m AS L, blueteammds AS b
WHERE l.md Like (left(b.md,4)+"*") And [md aware]=True
ORDER BY l.md, l.lastfill;

this is my query.

when i take out blueteammds as b and expand b.md to be blueteammds.md it asks me for the value when i try to run the query. Why?

also, on this query, i cannot update fields. i'm using this to pull data for the blueteam, and i want them to open this up then change a value in one column (a yes no). how can i accomplish this?

 

[alvechurchdata]

I think it's the "Like" that prevents the update. If you want to be able to update a query then Access needs to be able to work out which records are to be changed in the base tables. The join on [TT]l.md Like (left(b.md,4)+"*")[/TT] is not precise enough.

It looks as though the first four characters of b.md contain the foreign key back into LocalList but are then followed by some extra characters. If this is the case then you are putting two meanings into a single field and you ought to separate them.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

What about replacing this:
WHERE l.md Like (left(b.md,4)+"*")
with this ?
WHERE Left(l.md,4)=Left(b.md,4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

The other thing that makes it non-updatable is that its a cross join. Use PHV's suggestion and convert it to an inner join

SELECT l.MD, l.Patient, l.SSN, l.Drug, l.LASTFILL

FROM Locallist6m AS L [COLOR=blue]INNER JOIN[/color] blueteammds AS b
     [COLOR=blue]ON Left(l.md,4)=Left(b.md,4)[/color]

WHERE [md aware]=True

ORDER BY l.md, l.lastfill;

 

[NSmoller]

thanks for your help

the reason for the "like" statement is because the data we are getting from a file dump is never consistent. i have created a doctor table trying to make this consistent. it basically converts the current file dump to something close to how we view it for consistency.

for the purpose of my education, please verify this is true : the reason i want an inner join is because i REQUIRE a match in both tables, right?
if i wanted to do a table that gave me a list of the doctors that are NOT on the blue team, i could do an outer join (Left join) and search for null values in the one column?

is there anything special about a right join or is it literally just a left join switched? thanks alot

 

[PHV]

Have a look here:

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

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886