Qry not pulling bound column problem

[AJ]

I have a qry where I pull records from a table. When I run this qry all is
fine, I get the bound column (1) returned which is the text I want not the
primary key which is in column(0).
I do the same for another table... all is fine. Note these 2 were created
using the design view of Query... I'm not a programmer... just a chemist
trying to build a database.

Then I have a Union query to pull the 2 above together. This one returns
column(0) not column(1) as I need. If you ask why I do the queries this
way... only way I know how... sorry.

I'm not sure posting my code helps at this point.

Thanks to anyone who can help, it is greatly appreciated.

Thank you,

 

[Jeanette Cunningham]

AJ,
a union query would select both fields from tableX and both fields from
tableY.
This union query below:

Select TableX.FieldA, TableX.FieldB From TableX
Union
Select TableY.FieldA, TableY.FieldB From TableY

will give you 2 fields in the union query.

Jeanette Cunningham

 

[John W. Vinson/MVP]

I have a qry where I pull records from a table. When I run this qry all is
fine, I get the bound column (1) returned which is the text I want not
the
primary key which is in column(0).
I do the same for another table... all is fine. Note these 2 were created
using the design view of Query... I'm not a programmer... just a chemist
trying to build a database.

Hi fellow chemist!

You're another victim of Microsoft's misfeature, the Lookup Wizard. Your
table and query APPEAR to contain the text... but they don't. The combo box
conceals the actual content of the table from view.

If you want to see the text, use a query joining your main table to the
lookup table and select the text from the lookup table (and, if you want to
see both the ID and the text, select the ID from either table). If you don't
want to see the text, just base the query on the main table, and use the
Lookup tab in the field properties of this field to change it from Combo Box
to Textbox, so you can see what is actually there. It is NOT necessary to
use a UNION query.

 

[AJ]

I need the Union Qry... or so I think I do because... to use Jeanette's
example I am pulling...

select Tablex.fieldA,tablex.fieldB from Tablex
union
select tabley.fieldA,tableY.fieldB from TableY

to get 2 fields.

I do need to pull info from these 2 queries together for a report, if there
is a way to to that and not use the union query I am more than happy to learn
that. I just need the text too.

Thank you

 

[John W. Vinson/MVP]

I need the Union Qry... or so I think I do because... to use Jeanette's
example I am pulling...

select Tablex.fieldA,tablex.fieldB from Tablex
union
select tabley.fieldA,tableY.fieldB from TableY

to get 2 fields.

What are TableX and TableY? How (if at all) are they related? What do they
have to do with the rowsource of a combo box? What is the query mentioned in
your original post?

The UNION query will show ALL records in TableX and ALL records in TableY,
with no relationship between them; if FieldA is the ID and FieldB is the
description text, I don't see how this query is relevant to your original
post (which I understood to be about a Combo Box in a query datasheet).

 

[AJ]

sorry, it is hard to explain what you don't understand.

both tables have data about products that are on hold. They are on hold for
totally different reasons, thus why 2 tables. I'm trying to create one
report that tells me everything in the building on hold and why, thus why I'm
trying to pull information from both places.

I did set up each table with the look up wizard so when filling out the form
you select from the combobox, This is set up in both tables.

Then what I did was make a query off each table to pull just the "hold"
product info I needed, then I wrote the Union qry to pull the data from each
of the other 2 queries so the info is together for the report.

Where I ended up was that everything worked perfect and I got teh info
needed with the exception that the Union qry is reporting #s instead of text.


So, hopefully to answer your question is that programmatically the tables
are not related at all, functionally however they both have data I want in
one report.

I hope this explains the background better and helps you to assist.

Thank you.

 

[Jeanette Cunningham]

AJ,
It would be much easier if
1. you had only one table for products on hold.
The table would have an extra field (column) for reason on hold.
Then you won't need the union query.

2. Using the lookup wizard on tables is not popular among access developers.
It causes many problems, and for ease of developing your database, it is
best avoided.

3. Instead of the lookup wizard on your table, use that extra field to hold
the reason for the product being on hold.

If there are several main reasons for a product being on hold, you can
create a separate table for the 'on hold' reasons.
This table would have 2 fields, a primary key field called OnHoldID,
autonumber, and a reason field.
Your product table would have the OnHoldID as a foreign key and there would
be a one to many relationship between the 2 tables.
Make the join type to show all the records from products and only the
matching records from the OnHold table.
The users would have a drop down box to choose the reasons for a product
being on hold.


Jeanette Cunningham