New to Queries

[Ray C]

Hi. I am trying ti get my head round queries and wanted a form to show all
the "Stock" items that were booked out to a certain Contract. I tried the
Wizard and the only thing that was close to what I was looking for is a
Select Query "Find Duplicates". Close but not quite right as I am wanting to
find and display all the records that match the Text Box "Contract No". The
wizard produces a form showinhg all the duplicates and the query has a syntax
of "In (SELECT [ContractNo] FROM [tbl_Stock] As Tmp GROUP BY [ContractNo]
HAVING Count(*)>1 )" I have tried inserting "where {Contract No] =
Me.Contractno" in varoius places and different guises but I always get some
kind of error, I appreciate I am fishing blindly about here because I don't
understand the Query Language but can anyone help please?

Thanks RayC

 

[Klatuu]

Okay, first, select queries from the database window.
Select New to create a new query
Select the table you want the query based on
In the design grid, select all the fields you want to include in your query.
You can do that by double clicking on the field name in the upper portion
of the query where the table is shown, typing the name in the Field row of
the grid, or putting the cursor in a field cell and using the drop down to
select the field.

Now run the query to see that you are getting what you want so far.

Now to limit it to a specific contract put the cursor in the Criteria row of
the ContractNo column. You can enter a specific contract number or if you
want to be able to pull different contract numbers, you can put this in the
Criteria cell:
[Enter Contract Number:]

When you put that in and run the query, you will get an Input Box with the
text just as you entered - Enter Contract Number: Enter the contract number
and your query will return only records where the contract number is equal to
what you typed in.

 

[Ray C]

Thats Great, Thanks for your help.

If I put in =Forms. ContractForm.[ContractNo] That should return the
contract number currently entered in the forms Text Box and display those
that Match, or have I got my brackets and comma's all mixed up?

Thanks RayC

Okay, first, select queries from the database window.
Select New to create a new query
Select the table you want the query based on
In the design grid, select all the fields you want to include in your query.
You can do that by double clicking on the field name in the upper portion
of the query where the table is shown, typing the name in the Field row of
the grid, or putting the cursor in a field cell and using the drop down to
select the field.

Now run the query to see that you are getting what you want so far.

Now to limit it to a specific contract put the cursor in the Criteria row of
the ContractNo column. You can enter a specific contract number or if you
want to be able to pull different contract numbers, you can put this in the
Criteria cell:
[Enter Contract Number:]

When you put that in and run the query, you will get an Input Box with the
text just as you entered - Enter Contract Number: Enter the contract number
and your query will return only records where the contract number is equal to
what you typed in.

--
Dave Hargis, Microsoft Access MVP

Hi. I am trying ti get my head round queries and wanted a form to show all
the "Stock" items that were booked out to a certain Contract. I tried the
Wizard and the only thing that was close to what I was looking for is a
Select Query "Find Duplicates". Close but not quite right as I am wanting to
find and display all the records that match the Text Box "Contract No". The
wizard produces a form showinhg all the duplicates and the query has a syntax
of "In (SELECT [ContractNo] FROM [tbl_Stock] As Tmp GROUP BY [ContractNo]
HAVING Count(*)>1 )" I have tried inserting "where {Contract No] =
Me.Contractno" in varoius places and different guises but I always get some
kind of error, I appreciate I am fishing blindly about here because I don't
understand the Query Language but can anyone help please?

Thanks RayC

 

[Klatuu]

You don't need the = sign. In a Query, I would use this syntax:

[Forms]![ContractForm]![ContractNo]

It goes in the Criteria cell of the ContractNo column
--
Dave Hargis, Microsoft Access MVP

Thats Great, Thanks for your help.

If I put in =Forms. ContractForm.[ContractNo] That should return the
contract number currently entered in the forms Text Box and display those
that Match, or have I got my brackets and comma's all mixed up?

Thanks RayC

Okay, first, select queries from the database window.
Select New to create a new query
Select the table you want the query based on
In the design grid, select all the fields you want to include in your query.
You can do that by double clicking on the field name in the upper portion
of the query where the table is shown, typing the name in the Field row of
the grid, or putting the cursor in a field cell and using the drop down to
select the field.

Now run the query to see that you are getting what you want so far.

Now to limit it to a specific contract put the cursor in the Criteria row of
the ContractNo column. You can enter a specific contract number or if you
want to be able to pull different contract numbers, you can put this in the
Criteria cell:
[Enter Contract Number:]

When you put that in and run the query, you will get an Input Box with the
text just as you entered - Enter Contract Number: Enter the contract number
and your query will return only records where the contract number is equal to
what you typed in.

--
Dave Hargis, Microsoft Access MVP

Hi. I am trying ti get my head round queries and wanted a form to show all
the "Stock" items that were booked out to a certain Contract. I tried the
Wizard and the only thing that was close to what I was looking for is a
Select Query "Find Duplicates". Close but not quite right as I am wanting to
find and display all the records that match the Text Box "Contract No". The
wizard produces a form showinhg all the duplicates and the query has a syntax
of "In (SELECT [ContractNo] FROM [tbl_Stock] As Tmp GROUP BY [ContractNo]
HAVING Count(*)>1 )" I have tried inserting "where {Contract No] =
Me.Contractno" in varoius places and different guises but I always get some
kind of error, I appreciate I am fishing blindly about here because I don't
understand the Query Language but can anyone help please?

Thanks RayC

 

[John W. Vinson]

If I put in =Forms. ContractForm.[ContractNo] That should return the
contract number currently entered in the forms Text Box and display those
that Match, or have I got my brackets and comma's all mixed up?

Need more brackets and one less blank. Blanks are meaningful!! Try

=[Forms]![ContractForm]![ContractNo]


John W. Vinson [MVP]