More than obe value to lookup? (1 Viewer)

Gasman · Aug 7, 2017

Hi everyone,

I have a simple form that supplies an ID value to a query.

Code:

SELECT Links.CrewID, Links.RankID, Links.ShipID, Links.DatesID, Crew.Surname, Crew.Initials
FROM Crew INNER JOIN Links ON Crew.CrewID = Links.CrewID
WHERE (((Links.CrewID)=[Forms]![frmServedWith]![txtCrewID]) AND ((Links.ShipID)<>26 And (Links.ShipID)<>27));

How would I go about allowing it to look for one or two addtional ID values at the same time please?

plog · Aug 7, 2017

You will need more textCrewID inputs

Code:

...WHERE ((Links.CrewID=[Forms]![frmServedWith]![txtCrewID]) OR (Links.CrewID=[Forms]![frmServedWith]![txtCrewID2]) OR (Links.CrewID=[Forms]![frmServedWith]![txtCrewID3]))...

Auntiejack56 · Aug 7, 2017

Primarily, if the IDs are not 'ad hoc', but in a queryable structure, you could also use an 'In' subselect. Which allows you to select tbo or mbre values ahem.

Also, ShipID 26 and ShipID 27 are some sort of avoided craft in this query, maybe they're submarines or something? So I would be tempted
to put an informative flag in the Links Table eg IsSubmarine. It makes the business rules more visible (a big plus) and your SQL neater and
easier for the next guy/gal. In the SQL below I'm pretending you've done that (of course it may not be appropriate, but it's just a suggestion).

Lastly, you can see I've aliased the table names - just makes it less cluttered IMHO, but other people prefer to leave them in for readability.

Code:

SELECT L.CrewID, L.RankID, L.ShipID, L.DatesID, C.Surname, C.Initials
FROM Crew as C INNER JOIN Links as L ON C.CrewID = L.CrewID
WHERE L.CrewID in (SELECT CrewID FROM Links WHERE Links.ShipID = [Forms]![frmServedWith]![txtShipID]) AND Not L.IsSubmarine;

Jack

Gasman · Aug 8, 2017

@AuntiJack56
The selection is from a listbox, currently just the one selected item.
I then take that value and put it into a hidden textbox on the form to refer to more easily in the query.

The 26 and 27 IDs are not ships but Standy and Study Leave, which at the time I treated a s a ship for data entry puposes.

Auntiejack56 said:
Primarily, if the IDs are not 'ad hoc', but in a queryable structure, you could also use an 'In' subselect. Which allows you to select tbo or mbre values ahem.

Also, ShipID 26 and ShipID 27 are some sort of avoided craft in this query, maybe they're submarines or something? So I would be tempted
to put an informative flag in the Links Table eg IsSubmarine. It makes the business rules more visible (a big plus) and your SQL neater and
easier for the next guy/gal. In the SQL below I'm pretending you've done that (of course it may not be appropriate, but it's just a suggestion).

Lastly, you can see I've aliased the table names - just makes it less cluttered IMHO, but other people prefer to leave them in for readability.

Code:

SELECT L.CrewID, L.RankID, L.ShipID, L.DatesID, C.Surname, C.Initials FROM Crew as C INNER JOIN Links as L ON C.CrewID = L.CrewID WHERE L.CrewID in (SELECT CrewID FROM Links WHERE Links.ShipID = [Forms]![frmServedWith]![txtShipID]) AND Not L.IsSubmarine;

Jack

Gasman · Aug 9, 2017

I remembered I had tried something along these lines in another DB.
Dug into that and I found I did it with a where param on opening the report, building ip an IN list.

Perhaps not the most efficient way of doing it, but it works, so happy with that.

More than obe value to lookup? (1 Viewer)

Gasman

Enthusiastic Amateur

plog

Banishment Pending

Auntiejack56

Registered User.

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread