Append field from one query to another query?

[Guest]

I have a form with a combo box with a query as recordsource. It should show
company names and alternate names called customers in the box.

I am modeling my program on a paper workorder form the mechanics use. They
can enter a customer name or the actual client name on the paper form. My
computer form looks similar to the paper form and uses the combo box so the
person entering the data from the workorder can select the “customerâ€.


Currently I have created a Customer table with ClientID as key and Customer
as the other field. This table holds the possible customer names, which are
actually container owners names such as Matson, Cosco, SeaLand, etc.
ClientID is the same as on my Clients table.

The Clients table has ClientID as the key and a CompanyName field I want to
show also in the combo box along with Customer from the Customer table.

I’m not loking at my program as I write this, but I believe I have the
Customer table as a relationship of one-to-many, with the Clients table,
ClientID being on the one side and ClientID in the Customer table as the
many..


Can I show both CompanyName and Customer in the same combo box using a query?

If so, how would I accomplish this?

Any suggestions or help would be greatly appreciated.

Thanks,

Chuck

 

[Jeff Boyce]

Chuck

I'm having trouble visualizing your situation, but it sounds like you could
use a query to join Client and Customer tables, returning the ID and
whichever fields you wish to display.

Or are you considering using "cascading combo boxes", which would allow you
to first select a Client (the "one" side, right?), then select, in a second
combo box, from only those Customers (the "many" side, right?) belonging to
that Client?

 

[Guest]

Jeff,

In the scenario mentioned there is a single client: companyname. However
the work order form may have that name OR a different name which is called:
customer. The customer is on a separate table which holds all the possible
customer names that might be entered on the work order.

Because I don’t want the current method of filling out the work orders to
change I thought having a single list available in a combo box with both
companyname and customer name on it (there is only one column in the combo
box) would make for faster filling out of the form as there are a lot of
fields to fill out and selecting from a combo box would be faster and more
accurate than having to type in a name (if the correct one was not showing).

That being said, whatever shows in the combo box will not change anything in
the system as it is only a display field, but it should reflect what the
mechanic has written on the paper work order form. Note that the person
filling out the paper work order form is not going to be the person entering
the data into the computer form.

The effect I want, said in another way is similar to your “cascading combo
boxes†idea.
My program, however, initializes the combo box with the actual client name
(companyname). However the work order may say something different in the
Customer field. If it does, it should be one of the possible customer names
(customer). I want those possible names in the combo box so one can be
selected to match what is on the work order when necessary.

Maybe having both companyname and customer in the combo box is overkill as
it works fine with just having the customer names in the combo box. My
thinking that having the companyname also in the combo box is a safety
feature in case the user selected a customer from the combo box by mistake
and then could reselect the original name that showed in the box without
having to retype it in.

Thanks,

Chuck

 

[Guest]

Jeff,

After reading some of the answers to query questions I think I have found
how to do what I want to do.

I think something like this will work:

SELECT companyname FROM Clients
UNION
SELECT customer FROM Customers

I think that will append the two fields I need into one table.
If so, then that query could be the recordsource on my combo box.
I'll try it tonite or as soon as I get a chance.

Thanks,
Chuck