Creating a Paramater Query

[Guest]

I have an access 2003 trouble ticket database. One of my columns in the
trouble ticket table is a drop down list of customers. I have a second table
of all the customer names that supply the drop down list. I am trying to
create a query for the users that will prompt them to input the customer name
and then supply all the trouble tickets (opened or closed) that the customer
has had.

Every way I have tried to build a query for this, it returns 0 results. If
anyone can give me some step by step instructions on how to get this query
written, it would be most appreciated.

Thanks.

 

[Roger Carlson]

I assume you are using a LookUp control on the table, correct? Never - ever
do this!!!!! The reason is precisely the problem you are having. What you
see displayed in the field is the name, but what is actually stored is the
primary key value of the other table. This means that no matter what text
value you put in, it will be wrong.

Change the LookUp tab from ComboBox to Textbox and see exactly what is
stored.

What you should do is create a query, Joining the two tables, but use the
Customer Name field from the Customers table. Then your parameter query
will work.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Also, there is a customerID field linked between the customers table and the
trouble ticket table. If I put [Input Customer] as the criteria for the
customers column in my query I get back 0 results.

How do you get a paramater query to work that needs to pull information from
two tables (in this case, the customer name from the customer table and all
the trouble ticket information from the trouble ticket table)?

Thanks,
Roxanne

 

[Guest]

Roger,

Ok I changed the lookup to text box. Now, the tables are linked together by
a customersID. If I create a join between "customer" from the trouble ticket
table with "company name" from the customers table and then try to run a
parameter query, I get a "Type mismatch in expression" error. The data type
of customer is long integer, but I have changed the lookup to "text box."

Any idea what I am doing wrong?
Thanks so much!
Roxanne

Also, there is a customerID field linked between the customers table and the
trouble ticket table. If I put [Input Customer] as the criteria for the
customers column in my query I get back 0 results.

How do you get a paramater query to work that needs to pull information from
two tables (in this case, the customer name from the customer table and all
the trouble ticket information from the trouble ticket table)?

Thanks,
Roxanne

I have an access 2003 trouble ticket database. One of my columns in the
trouble ticket table is a drop down list of customers. I have a second table
of all the customer names that supply the drop down list. I am trying to
create a query for the users that will prompt them to input the customer name
and then supply all the trouble tickets (opened or closed) that the customer
has had.

Every way I have tried to build a query for this, it returns 0 results. If
anyone can give me some step by step instructions on how to get this query
written, it would be most appreciated.

Thanks.

 

[Roger Carlson]

That's the point. You still join it on CustomerID from both tables. But in
your query, when you Join the tables, you *display* the Customer Name from
the Customer table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,

Ok I changed the lookup to text box. Now, the tables are linked together by
a customersID. If I create a join between "customer" from the trouble ticket
table with "company name" from the customers table and then try to run a
parameter query, I get a "Type mismatch in expression" error. The data type
of customer is long integer, but I have changed the lookup to "text box."

Any idea what I am doing wrong?
Thanks so much!
Roxanne

Also, there is a customerID field linked between the customers table and the
trouble ticket table. If I put [Input Customer] as the criteria for the
customers column in my query I get back 0 results.

How do you get a paramater query to work that needs to pull information from
two tables (in this case, the customer name from the customer table and all
the trouble ticket information from the trouble ticket table)?

Thanks,
Roxanne

I have an access 2003 trouble ticket database. One of my columns in the
trouble ticket table is a drop down list of customers. I have a second table
of all the customer names that supply the drop down list. I am trying to
create a query for the users that will prompt them to input the customer name
and then supply all the trouble tickets (opened or closed) that the customer
has had.

Every way I have tried to build a query for this, it returns 0 results. If
anyone can give me some step by step instructions on how to get this query
written, it would be most appreciated.

Thanks.

 

[Guest]

So when I create a query in design mode and I've brought all the columns from
the trouble ticket table into the query, my two tables show being joined by
CustomerID. How do you "display" the Customer name from the Customers table?
I've put [Input Customer Name] as the Criteria under the "customer" column
from the trouble ticket table, but what other coding needs to be specified in
the query (when in design view) that will *display* the customer name?

Thanks for your patience and help!
Roxanne

That's the point. You still join it on CustomerID from both tables. But in
your query, when you Join the tables, you *display* the Customer Name from
the Customer table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,

Ok I changed the lookup to text box. Now, the tables are linked together by
a customersID. If I create a join between "customer" from the trouble ticket
table with "company name" from the customers table and then try to run a
parameter query, I get a "Type mismatch in expression" error. The data type
of customer is long integer, but I have changed the lookup to "text box."

Any idea what I am doing wrong?
Thanks so much!
Roxanne

Also, there is a customerID field linked between the customers table and the
trouble ticket table. If I put [Input Customer] as the criteria for the
customers column in my query I get back 0 results.

How do you get a paramater query to work that needs to pull information from
two tables (in this case, the customer name from the customer table and all
the trouble ticket information from the trouble ticket table)?

Thanks,
Roxanne

:

I have an access 2003 trouble ticket database. One of my columns in the
trouble ticket table is a drop down list of customers. I have a second table
of all the customer names that supply the drop down list. I am trying to
create a query for the users that will prompt them to input the customer name
and then supply all the trouble tickets (opened or closed) that the customer
has had.

Every way I have tried to build a query for this, it returns 0 results. If
anyone can give me some step by step instructions on how to get this query
written, it would be most appreciated.

Thanks.

 

[Roger Carlson]

You need to drag the Customer Name field from the Customer table into the
query as well and that's where you put your parameter prompt.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

So when I create a query in design mode and I've brought all the columns from
the trouble ticket table into the query, my two tables show being joined by
CustomerID. How do you "display" the Customer name from the Customers table?
I've put [Input Customer Name] as the Criteria under the "customer" column
from the trouble ticket table, but what other coding needs to be specified in
the query (when in design view) that will *display* the customer name?

Thanks for your patience and help!
Roxanne

That's the point. You still join it on CustomerID from both tables. But in
your query, when you Join the tables, you *display* the Customer Name from
the Customer table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger,

Ok I changed the lookup to text box. Now, the tables are linked

together
by

a customersID. If I create a join between "customer" from the trouble ticket
table with "company name" from the customers table and then try to run a
parameter query, I get a "Type mismatch in expression" error. The

data
type

of customer is long integer, but I have changed the lookup to "text box."

Any idea what I am doing wrong?
Thanks so much!
Roxanne

:

Also, there is a customerID field linked between the customers table

and
the

trouble ticket table. If I put [Input Customer] as the criteria for the
customers column in my query I get back 0 results.

How do you get a paramater query to work that needs to pull

information
from

two tables (in this case, the customer name from the customer table

and
all

the trouble ticket information from the trouble ticket table)?

Thanks,
Roxanne

:

I have an access 2003 trouble ticket database. One of my columns

in
the

trouble ticket table is a drop down list of customers. I have a second table
of all the customer names that supply the drop down list. I am

trying
to

create a query for the users that will prompt them to input the customer name
and then supply all the trouble tickets (opened or closed) that

the
customer

has had.

Every way I have tried to build a query for this, it returns 0 results. If
anyone can give me some step by step instructions on how to get

this
query

written, it would be most appreciated.

Thanks.