Comparing row based on contents to another tables rows in Access SQL

Question

Hi I'm working on a project and for time constraint reasons I need to keep working in Access which may be the root of all my problems but maybe there's hope.

I have a database that includes a table ANSWERS filled with input for users "wants" there are multiple columns which each correspond to an answer to a different question asking if they, Don't Care, Want, or Need something.

EG: Answers:

    Bacon  |  Ham   |  Sausage
  ________________________________
1     0        0          2
2     2        1          0
3     0        2          0
4     1        1          1

(0 = Don't Care, 1 = Want, 2 = Need)

I want to compare a row from table Answers to the Available table.

EG: Available:

    Bacon  | Ham    |  Sausage
   ________________________________
1    0        1           0
2    0        0           0
3    1        1           1
4    1        1           0

(0 = Unavailable, 1 = Available)

So I would want to compare row 1 from Answers to Available so because row 1 includes sausage=2 then I would want to receive row 3 from Available because sausage=1.

I'd be happy receiving the entire row, or the row ID and a "1" for the rows being a match.

Ultimately I'd need to do this for all each of the rows in Answers.

Any ideas are appreciated, I was thinking using Intersect might work but since that doesn't work in access. I've also considered joining the tables, I could also change data variables or formats if necessary.

Thanks very much

Edit: Don't Care was previously Don't Want. Changed for clarity.

I'm having trouble getting my head around the idea that someone would select the "Don't Want" option for [Bacon]. ;) — Gord Thompson
– Gord Thompson, Commented Nov 25, 2013 at 19:26
I'm with Gord on this. But Bacon aside, more explanation may be helpful- I suspect your data structure is working against you. For instance, Bacon, Ham or Sausage should be rows and Availability a single column. Then your query is easy. But if there are other variables (Locations?) then things change a bit. — craig.white
– craig.white, Commented Nov 25, 2013 at 19:56
Ok sure, I agree about the bacon haha. I just tried to simplify it for posting. In available each of the rows is a survey filled out by the user and it is holding those replies. In Available it is the list of available (lets say) menus in this case. In every case a user NEEDS something I need to pull all the menus that have what they need. Each row in Answers also contains session info, time, date, zip. I could however break that out and have a table just containing the results as you said, but in my actual DB I have 13 cols of wants, needs etc. — SOG
– SOG, Commented Nov 25, 2013 at 20:42
I was re-reading what I wrote, and I'm not sure its helpful. So I'll try and explain actual implementation. From the query I would want to be able to say which Menus in Available fit the users criteria. and I would want to know which ones didn't fit and what they were lacking. So if one user wanted bacon and ham they could only be given menu 3 or 4 and not menu 2 because it doesn't offer bacon or ham and not menu 1 because it doesn't offer bacon. Hopefully thats clearer. Thanks for your help — SOG
– SOG, Commented Nov 25, 2013 at 21:16
Makes a little more sense; one question though- does 'Not Want' indicate that if it is available on a menu then that menu should be excluded from the result set? That will really limit your results. — craig.white
– craig.white, Commented Nov 25, 2013 at 21:35

craig.white · Accepted Answer · 2013-11-25 22:00:09Z

1

Give this a try: SELECT tblAnswers.UserID, IIf([tblAnswers].[bacon]>0 And [tblMenus].[Bacon]<>0,[MenuID],Null) AS BaconMenu, IIf([tblAnswers].[Ham]>0 And [tblMenus].[Ham]<>0,[MenuID],Null) AS HamMenu, IIf([tblAnswers].[Sausage]>0 And [tblMenus].[Sausage]<>0,[MenuID],Null) AS SausageMenu FROM tblAnswers, tblMenus WHERE (((IIf([tblAnswers].[bacon]>0 And [tblMenus].[Bacon]<>0,[MenuID],Null)) Is Not Null)) OR (((IIf([tblAnswers].[Ham]>0 And [tblMenus].[Ham]<>0,[MenuID],Null)) Is Not Null)) OR (((IIf([tblAnswers].[Sausage]>0 And [tblMenus].[Sausage]<>0,[MenuID],Null)) Is Not Null));

Just paste that into a SQL view make Query window (After changing the table and column names to match yours) You will obviously need to tweak it as reality needs, but it does what you asked for with the data provided.

answered Nov 25, 2013 at 22:00

craig.white

4272 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SOG Over a year ago

This is great. Thank you very much, it does what I need it to do and is a great foundation for the rest of the work. Thank you again.

Collectives™ on Stack Overflow

Comparing row based on contents to another tables rows in Access SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related