Query "And" on same field (1 Viewer)

[whitedove]

I know this might sound simple. But I want to make a query that would show ID 1 once based on the criteria of having both Home and Office. I will apply the concept on a bigger database.

ID is from table 1
Date and Location from table 2


Thank you

 

[plog]

Please provide example data. Show what data your tables hold (include table and field names). Then based on that sample data, show what you expect your query to return. Be sure to include enough sample data to cover all cases.

 

[whitedove]

Sorry I actually an image link but I think it was not posted because I am still new
This was the table in the image

ID Date Location
--- ------ --------
1 1/05/2015 Home
1 2/05/2015 Office
2 2/23/2014 Home
3 4/15/2012 Office

Thank you

 

[plog]

I assume that's starting data. Now tell me what you expect your query to return.

Looking at it, I suspect its also not enough starting data to cover all cases you might encounter.

 

[whitedove]

I would like to put IDs with both home and office so that the query would return

ID - Date - Location
--- ------ ----------
1 - (not important) - Home and Office
- (if not possible then show home or office for location)

The most important thing is the query would show only ID number 1 and show it only once. I tried putting Home and Office in the criteria but it return no result. If I put home or office it shows all records. I hope I made my point clear.

Thank you

 

[plog]

Try this SQL:

SELECT Table2.ID
FROM Table2
WHERE (((Table2.Location)="Home" Or (Table2.Location)="Office"))
GROUP BY Table2.ID
HAVING (((IIf(Max([Location])<>Min([Location]),1,0))=1));

 

[whitedove]

The solution did not work. It returned empty table. I have created a database with those criteria and attached it so you can check it. Thanks

 

[plog]

That's because you incorrectly told me your field names.

This isn't a hard fix. Go into design view and give it a shot.

 

[whitedove]

I know it might not be hard for you. If I can give it a shot I would not have asked. Sorry but I could not figure it out.

 

[plog]

You have the wrong ID field in your query. You need to GROUP BY the field called Table1ID.

 

[vbaInet]

Something like this?

http://allenbrowne.com/func-concat.html

 

[whitedove]

Field Table1ID is not even in the query. The ID is coming from table 1, table1ID is coming from table 2.
how to do the query showing just ID 1 with the criteria of Home and Office?

 

[whitedove]

@vbaInet I don't care anymore what value shows in the location field. All I care about to filter the criteria correctly and show only the ID. Please help!
I cannot apply two criteria (Home and Office on the same column)

Please help. I am new to access

Thanks

http://www.access-programmers.co.uk/forums/member.php?u=73569

 

[plog]

You are using the wrong ID field in your query, you need to replace it with Table1ID

 

[vbaInet]

I'll help out whilst plog is away.

Where does Table1 come into play? I don't see it used in your query.

If you were to use just Table2, then the SQL of the query would be:

SELECT Table1ID
FROM Table2
WHERE [Location] = "Home" OR [Location] = "Office"
GROUP BY Table1ID;

Or

SELECT DISTINCT Table1ID
FROM Table2
WHERE [Location] = "Home" OR [Location] = "Office";

... copy and paste into the SQL view of any query in the db you posted.

 

[vbaInet]

I'll help out whilst plog is away.

I take that back

 

[whitedove]

I am so sorry for confusing you. I actually put the code that he gave me at the beginning to show him that it is not working. Please see attached below. This is what I meant with my query.
ID from table 1
DOB and Location from table 2
I want to show ID only once based that it has both Home and Office.
Sorry again for confusing you.

 

[vbaInet]

My last comment was in reference to the fact that plog was still around, but I think this time he's gone to sleep so...

What was already given could have been used by including the Table1, joining it to Table2 and swapping the ID field for the Table1's ID field. plog already mentioned this but since you're new to Access:

SELECT DISTINCT Table1.ID
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.[Location] = "Home" OR Table2.[Location] = "Office";

 

[whitedove]

This returned only one column "ID" with all four record and no DOB or Location column. I wanted only to show the record with ID 1 which would only come up if the query is Office and Home instead of Office or Home. But Office and Home don't work because it return empty record

I hope you do not sleep too

 

[vbaInet]

Ok, I understand your requirement now:

SELECT Table1.ID
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.[Location] = "Home" OR Table2.[Location] = "Office"
GROUP BY Table1.ID
HAVING Count(Table1.ID) > 1;