SQL Help (Right forum?)

[pikachu1070]

I was just wondering how to use the MINUS function in access.
whenever i try it always tells me that there is an error on the FROM clause and then highlights my MINUS
i assume this means that it might not recognise MINUS
so, is there another word to use in its place?

 

[jdraw]

I was just wondering how to use the MINUS function in access.
whenever i try it always tells me that there is an error on the FROM clause and then highlights my MINUS
i assume this means that it might not recognise MINUS
so, is there another word to use in its place?

Correct, Access Sql does not recognize MINUS based on my use of the MINUS operator in Oracle SQL.
Some M$oft SQL links of interest:
http://msdn.microsoft.com/en-us/library/aa140011(office.10).aspx
http://msdn.microsoft.com/en-us/library/aa140015(office.10).aspx
http://msdn.microsoft.com/en-us/library/aa139977(office.10).aspx

I don't believe there is a different operator to use. However, you may be able to use

Select 1 .... WHERE XXX not IN (Select 2...)
OR
some pattern along this line:
Select XX from Table1
LEFT JOIN Table2 ON FldTbl1= FldTbl2
Where FldTbl2 IS NULL

 

[SimonB1978]

Hi,

MINUS is not supported in Access. You'll have to use a NOT IN or a 'frustrated join' to replicate it...

If you post your query we could possible help you out.

Simon B.

 

[pikachu1070]

well, with NOT IN i tried

SELECT tbl_clients.[Client Name], tbl_client_day.clientID
FROM tbl_clients INNER JOIN tbl_client_day ON tbl_clients.clientID = tbl_client_day.clientID
WHERE tbl_clients.[Client Name] NOT IN (tbl_prog_day.[Client Name].Value)
ORDER BY tbl_clients.[Client Name];

basically im trying to remove all the values that have been selected in a multi value field (client name in tbl_prog_day)

 

[jdraw]

well, with NOT IN i tried

SELECT tbl_clients.[Client Name], tbl_client_day.clientID
FROM tbl_clients INNER JOIN tbl_client_day ON tbl_clients.clientID = tbl_client_day.clientID
WHERE tbl_clients.[Client Name] NOT IN (tbl_prog_day.[Client Name].Value)
ORDER BY tbl_clients.[Client Name];

basically im trying to remove all the values that have been selected in a multi value field (client name in tbl_prog_day)

You could try this

SELECT tbl_clients.[Client Name]
FROM tbl_clients
WHERE tbl_clients.[Client Name] NOT IN (SELECT tbl_prog_day.[Client Name]
FROM tbl_prog_day)
ORDER BY tbl_clients.[Client Name];

OR

SELECT tbl_clients.[Client Name]
FROM tbl_clients
LEFT JOIN tbl_prog_day on
tbl_clients.[Client Name] = tbl_prog_day.[Client Name]
WHERE
tbl_prog_day.[Client Name] IS NULL;

 

[SimonB1978]

Should be like this:

SELECT tbl_clients.[Client Name], tbl_client_day.clientID
FROM tbl_clients INNER JOIN tbl_client_day ON tbl_clients.clientID = tbl_client_day.clientID
WHERE tbl_clients.[Client Name] NOT IN (SELECT [Client Name] FROM tbl_prog_day)
ORDER BY tbl_clients.[Client Name];

if you have performance issued try a 'frustrated join'

SELECT tbl_clients.[Client Name], tbl_client_day.clientID
FROM (tbl_clients INNER JOIN tbl_client_day ON tbl_clients.clientID = tbl_client_day.clientID) LEFT JOIN tbl_prog_day ON tbl_clients.[Client Name] = tbl_prog_day.[Client Name]
WHERE tbl_prog_day.[Client Name] IS NULL
ORDER BY tbl_clients.[Client Name];

HTH,

Simon B.

 

[pikachu1070]

data type mismatch in criteria expression
cause tbl_prog_day.[Client Name] is a multi value field ^_^'

 

[SimonB1978]

damn jdraw... you're too fast...

 

[SimonB1978]

well... I have no experience with multi-valued fields in Access... but from my experience with other DBMS I wouldn't touch it with a ten foot pole....

I hope someone else can help...

 

[pikachu1070]

ok, i added a .Value to the relevant points and the code is working in the opposite way that i want
but you guys are all awesome, i love you so much, first actual change in any kind of right direction
wat it lists now is wat has been selected, i want wat hasnt been selected, but ive got a good code to work off now =D

 

[dfenton]

MINUS is not supported in Access.

Of course it is! You just have to use a back end whose SQL dialect supports it. Jet/ACE SQL does not, but you aren't limited to Jet/ACE when using Access.

 

[SimonB1978]

but that would be a pass-through query, which is executed on the back-end... not executed by Access...

 

[pikachu1070]

Ok, so what Ive decided to do is use a ListBox on the form to display the values that havent been selected. This code:

SELECT tbl_clients.[Client Name]
FROM tbl_clients LEFT JOIN tbl_prog_day on tbl_clients.[Client Name] = tbl_prog_day.[Client Name].Value
WHERE tbl_prog_day.[Client Name].Value IS NULL;

does just that, +1 (Y)
Now, the only thing i want to do now is to link it to a "dayID"
so that it displays all the values that havent been selected for "Monday"

So, eg. Clients For Monday=Bill, Bob, Dave and Jim
Clients for Tuesday=Ricky, Lester, Tim
User selects Monday
Listbox displays:
Bill
Bob
Dave
Jim
User Selects Bill and Dave
Listbox Displays Bob and Jim
User selects Tuesday
Listbox displays
Lester
Ricky
Tim

hope this makes sense to yall

 

[dfenton]

but that would be a pass-through query, which is executed on the back-end... not executed by Access...

It may come as a shock to you, but Access doesn't execute any of your SQL -- it hands it off to Jet/ACE, or to whetever other interface you're using to a different database engine (via passthrough over ODBC/Jet/ACE, or ODBCDirect, or ADO/OLEDB).