Query Question

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

I have a Query1 that say has 18 Horses Filtered , I have another query2 that
has 9 of those horses same horses filtered
can I add Query2 to Query1 and get an astrix(*) in query1 to show which
horses are not Filtered or Filtered either way, so 9 Horses will have an
astrix in Query1
 
You could use a subquery to do this, Bob:
http://allenbrowne.com/subquery-01.html

I'm assuming here that you used the word 'filtered' in a generic sense (i.e.
you used the Criteria in the query design), not in the specific sense of
applying a filter afterwards (e.g. by the toolbar buttons.)
 
Another method is to use a UNION. For example:

Select Name, '' As Filter from Horses
UNION
Select Name, '*' As Filter from Horses Where Breed = 'Arabian'

The result is a table with records from the first query thant have a blank
in the Filter field, and records from the second query that have a * in the
Filter field. You can modify the Where clauses for each query as needed, but
not that it will be ppossible to have the same horse show up multiple times.
Usually that would be a bad thing.

Ray
 
Thanks Ray, the problem being that query1 is controlling my list box and I
am tryng to get some astrix "*" next to a horse in the list box , that is or
is not in query2
Regards Bob
 
1. Create a query using both Query1 and Query2 as input tables.
In the upper pane of query design, there should be a line joining some field
(HorseID?) in Query1 to the matching field in Query2.

2. Double-click that join line.
Access pops up a dialog with 3 options.
Choose, "All records from Query1, and any matches from Query2."
(This is called an outer join.)

3. In the field row:
Query1.HorseName & IIf(Query2.HorseID Is Null, Null, " *")

If you want to do it with a subquery instead, it will look something like
this:
SELECT Query1.HorseName & IIf(EXISTS
(SELECT ID Query2.HorseID
FROM Query2
WHERE Query2.HorseID = Query1.HorseID), " *", Null)
FROM Query1;
 
Brilliant Allen, Thank you very much :-)
Is it Possible to have the Astrix before the Name!
Regards Bob Vance

Astrix: [qryOrderByHorseName].[Name] & IIf([qryNoHolding].[HorseID] Is
Null,Null," *")

SELECT qryOrderByHorseName.HorseID, [qryOrderByHorseName].[Name] &
IIf([qryNoHolding].[HorseID] Is Null,Null," *") AS Astrix,
qryOrderByHorseName.Name
FROM qryOrderByHorseName LEFT JOIN qryNoHolding ON
qryOrderByHorseName.HorseID = qryNoHolding.HorseID;
 
Sure: just swap the expression around.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Brilliant Allen, Thank you very much :-)
Is it Possible to have the Astrix before the Name!
Regards Bob Vance

Astrix: [qryOrderByHorseName].[Name] & IIf([qryNoHolding].[HorseID] Is
Null,Null," *")

SELECT qryOrderByHorseName.HorseID, [qryOrderByHorseName].[Name] &
IIf([qryNoHolding].[HorseID] Is Null,Null," *") AS Astrix,
qryOrderByHorseName.Name
FROM qryOrderByHorseName LEFT JOIN qryNoHolding ON
qryOrderByHorseName.HorseID = qryNoHolding.HorseID;

Allen Browne said:
1. Create a query using both Query1 and Query2 as input tables.
In the upper pane of query design, there should be a line joining some
field (HorseID?) in Query1 to the matching field in Query2.

2. Double-click that join line.
Access pops up a dialog with 3 options.
Choose, "All records from Query1, and any matches from Query2."
(This is called an outer join.)

3. In the field row:
Query1.HorseName & IIf(Query2.HorseID Is Null, Null, " *")

If you want to do it with a subquery instead, it will look something like
this:
SELECT Query1.HorseName & IIf(EXISTS
(SELECT ID Query2.HorseID
FROM Query2
WHERE Query2.HorseID = Query1.HorseID), " *", Null)
FROM Query1;
Click to expand...
Click to expand...
 
Thanks Allen, With this type of format I am getting the dot first to some
horse, can I get a " " In front of the rest of them so All horses will be
aligned together on the left of the list box?
Regards Bob

Astrix: IIf([qryNoHolding].[HorseID] Is Null,Null,"•") &
[qryOrderByHorseName].[Name]
Allen Browne said:
Sure: just swap the expression around.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Brilliant Allen, Thank you very much :-)
Is it Possible to have the Astrix before the Name!
Regards Bob Vance

Astrix: [qryOrderByHorseName].[Name] & IIf([qryNoHolding].[HorseID] Is
Null,Null," *")

SELECT qryOrderByHorseName.HorseID, [qryOrderByHorseName].[Name] &
IIf([qryNoHolding].[HorseID] Is Null,Null," *") AS Astrix,
qryOrderByHorseName.Name
FROM qryOrderByHorseName LEFT JOIN qryNoHolding ON
qryOrderByHorseName.HorseID = qryNoHolding.HorseID;

Allen Browne said:
1. Create a query using both Query1 and Query2 as input tables.
In the upper pane of query design, there should be a line joining some
field (HorseID?) in Query1 to the matching field in Query2.

2. Double-click that join line.
Access pops up a dialog with 3 options.
Choose, "All records from Query1, and any matches from Query2."
(This is called an outer join.)

3. In the field row:
Query1.HorseName & IIf(Query2.HorseID Is Null, Null, " *")

If you want to do it with a subquery instead, it will look something
like this:
SELECT Query1.HorseName & IIf(EXISTS
(SELECT ID Query2.HorseID
FROM Query2
WHERE Query2.HorseID = Query1.HorseID), " *", Null)
FROM Query1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thanks Ray, the problem being that query1 is controlling my list box
and I am tryng to get some astrix "*" next to a horse in the list box ,
that is or is not in query2
Regards Bob

Another method is to use a UNION. For example:

Select Name, '' As Filter from Horses
UNION
Select Name, '*' As Filter from Horses Where Breed = 'Arabian'

The result is a table with records from the first query thant have a
blank
in the Filter field, and records from the second query that have a *
in the
Filter field. You can modify the Where clauses for each query as
needed, but
not that it will be ppossible to have the same horse show up multiple
times.
Usually that would be a bad thing.

Ray



:

I have a Query1 that say has 18 Horses Filtered , I have another
query2 that
has 9 of those horses same horses filtered
can I add Query2 to Query1 and get an astrix(*) in query1 to show
which
horses are not Filtered or Filtered either way, so 9 Horses will have
an
astrix in Query1
Click to expand...
Click to expand...
Click to expand...
 
Subsititute a space (or spaces) instead of the Null as the middle argument
in the IIf().
 
Bob Vance said:
Thanks Allen, With this type of format I am getting the dot first to some
horse, can I get a " " In front of the rest of them so All horses will be
aligned together on the left of the list box?
Regards Bob

Astrix: IIf([qryNoHolding].[HorseID] Is Null,Null,".") &
[qryOrderByHorseName].[Name]
Allen Browne said:
Sure: just swap the expression around.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Brilliant Allen, Thank you very much :-)
Is it Possible to have the Astrix before the Name!
Regards Bob Vance

Astrix: [qryOrderByHorseName].[Name] & IIf([qryNoHolding].[HorseID] Is
Null,Null," *")

SELECT qryOrderByHorseName.HorseID, [qryOrderByHorseName].[Name] &
IIf([qryNoHolding].[HorseID] Is Null,Null," *") AS Astrix,
qryOrderByHorseName.Name
FROM qryOrderByHorseName LEFT JOIN qryNoHolding ON
qryOrderByHorseName.HorseID = qryNoHolding.HorseID;

1. Create a query using both Query1 and Query2 as input tables.
In the upper pane of query design, there should be a line joining some
field (HorseID?) in Query1 to the matching field in Query2.

2. Double-click that join line.
Access pops up a dialog with 3 options.
Choose, "All records from Query1, and any matches from Query2."
(This is called an outer join.)

3. In the field row:
Query1.HorseName & IIf(Query2.HorseID Is Null, Null, " *")

If you want to do it with a subquery instead, it will look something
like this:
SELECT Query1.HorseName & IIf(EXISTS
(SELECT ID Query2.HorseID
FROM Query2
WHERE Query2.HorseID = Query1.HorseID), " *", Null)
FROM Query1;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Thanks Ray, the problem being that query1 is controlling my list box
and I am tryng to get some astrix "*" next to a horse in the list box
, that is or is not in query2
Regards Bob

Another method is to use a UNION. For example:

Select Name, '' As Filter from Horses
UNION
Select Name, '*' As Filter from Horses Where Breed = 'Arabian'

The result is a table with records from the first query thant have a
blank
in the Filter field, and records from the second query that have a *
in the
Filter field. You can modify the Where clauses for each query as
needed, but
not that it will be ppossible to have the same horse show up multiple
times.
Usually that would be a bad thing.

Ray



:

I have a Query1 that say has 18 Horses Filtered , I have another
query2 that
has 9 of those horses same horses filtered
can I add Query2 to Query1 and get an astrix(*) in query1 to show
which
horses are not Filtered or Filtered either way, so 9 Horses will
have an
astrix in Query1
Click to expand...
Click to expand...
Click to expand...
 
Allen Browne said:
Subsititute a space (or spaces) instead of the Null as the middle argument
in the IIf().

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Thanks Allen, With this type of format I am getting the dot first to some
horse, can I get a " " In front of the rest of them so All horses will be
aligned together on the left of the list box?
Regards Bob

Astrix: IIf([qryNoHolding].[HorseID] Is Null,Null,"•") &
[qryOrderByHorseName].[Name]
Click to expand...
Click to expand...
 
Allen Browne said:
Subsititute a space (or spaces) instead of the Null as the middle argument
in the IIf().

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Thanks Allen, With this type of format I am getting the dot first to some
horse, can I get a " " In front of the rest of them so All horses will be
aligned together on the left of the list box?
Regards Bob

Astrix: IIf([qryNoHolding].[HorseID] Is Null,Null,"•") &
[qryOrderByHorseName].[Name]
Click to expand...
Click to expand...
 
Just BRILLIANT :), Thanks Allen........Regards Bob

Allen Browne said:
Subsititute a space (or spaces) instead of the Null as the middle argument
in the IIf().

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Bob Vance said:
Thanks Allen, With this type of format I am getting the dot first to some
horse, can I get a " " In front of the rest of them so All horses will be
aligned together on the left of the list box?
Regards Bob

Astrix: IIf([qryNoHolding].[HorseID] Is Null,Null,"•") &
[qryOrderByHorseName].[Name]
Click to expand...
Click to expand...
 
Back
Top