Search results

So I have to feed that Table (CustomerandProduct) manually? Kind of makes Access useless for me that way. Surely there must be a way out of this. Some advanced query perhaps? This is like the Access equivalent of brute force. Thank you for your answers though. How about using NOT EXISTS, NOT IN...

That's not possible. This is just a sample db to simulate the problem. My actual db is larger and more complex. I can't do away with lookup fields where new names, products can be added. Involves a proper front end with forms & vb codes.

The file has been uploaded.

Ok. Query designed as follows: SELECT Table1.Customer, Table2.Product, Table3.ID FROM Table1,Table2,Table3; table3 has two fields: customer and product (both are lookup fields) Now, if I select this query and the linked table (table3) in the Unmatched Query wizard (with the link being the...

Suppose there are two tables - customer and product. Also available is customerproduct (linked table containing customers and the products they bought).i want to design a query which will list products NOT bought by a customer i.e. each customer will be listed with products not bought by him...

Thanks for looking through it. I worked it out somewhat by using two different queries (top part and bottom part) and then creating a third query get the result. You are right, I have used too many parentheses making it unreadable. Query 1: SELECT Aviator.Number FROM Aviator INNER JOIN...

(SELECT Aviator.Prefix, Aviator.Number, Aviator.DateOfSeniority, Year([DateofSeniority]) FROM (Aviator INNER JOIN AviatorCourse ON Aviator.ID = AviatorCourse.AviatorID) WHERE (((AviatorCourse.CourseName)=9) AND ((Year([DateofSeniority]))=2007)))) LEFT JOIN (SELECT Aviator.Prefix...

Can INTERSECT help? How will I implement it in this example? I guess I will have to use INNER JOIN in access but how?

I am sure there must be a solution this. Is there an SQL statement I can use as my search string which will get me the result?

Yes. That's the issue. Since the checkboxes refer to the same field, I am not able to use the AND logic properly. I had done the debug thing too.

There is a junction table called tblNameItem which is recording what you have written. The query is based on that table.

Why couldn't you open the file? it is small enough. Saved in Access 2013. I have attached them again in older formats. test.mdb is in access 2000 format. The other one is in 2003 format.

query SQL: SELECT tblNameItem.User, tblNameItem.Item FROM tblNameItem; Report SQL (recordsource): SELECT tblNameItem.User, tblNameItem.Item FROM tblNameItem; VB code: Private Sub cmdOK_Click() Dim SearchString As String Dim LengthOfString As Integer If Me.chkBox = -1 Then...

I must emphasize that the logic should be "AND" for checkboxes and not "OR"

Ok. I am attaching the small database. I have removed the user combo box. Individual check boxes work. But when I select two boxes, the report is blank.

Here is the result of a query Name ItemBought Tom Box Tom Candle Tom Pen Bob Box Bob Pen A form is used to Select name (combo box) and items bought (check boxes). In this case, the form has a single combo box and three check boxes...

Is there a better date picker available? The one in Access takes ages to select an employee's Date of Birth (obviously not born this year)!

My issue is similar to http://http://www.access-programmers.co.uk/forums/showthread.php?t=257871 Except that I need the VBA code.

Junction table is not being used as I want one customerId to come up in case he satisfies both A and B. I don't want two separate entries.

Apologies. Here's the complete thing. I have a query involving multiple tables and sub-queries. Only the equipmentID field is multivalued and using a lookup from another table (containing equipmentlist). It is then indexed along with the customerID. This query is working fine in query mode...