Creating a query selecting multiple fields

[Guest]

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?

 

[Guest]

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.

 

[Guest]

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

 

[Guest]

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.

 

[Guest]

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

Ok I tried this in SQL. The first time I ran it says cannot find table. So I
changed YourTable to the name of one of the tables. The query ran but no
matter what I entered I never got results.

I'm using two tables. Table #1 has the information I'm searching for. I want
the field that I enter to have a "1" in the row. I want it to output certain
fields from both tables. I have it linked according to ID.

Do I need to establish more relationships with the two tables? Or what else
can I do to get this to work?

I appreciate the help!

Thanks!

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little


:

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

You gotta have both table in the query and joined on the common field.
Post the table and field names with the field datatype.
--
KARL DEWEY
Build a little - Test a little

Ok I tried this in SQL. The first time I ran it says cannot find table. So I
changed YourTable to the name of one of the tables. The query ran but no
matter what I entered I never got results.

I'm using two tables. Table #1 has the information I'm searching for. I want
the field that I enter to have a "1" in the row. I want it to output certain
fields from both tables. I have it linked according to ID.

Do I need to establish more relationships with the two tables? Or what else
can I do to get this to work?

I appreciate the help!

Thanks!

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks
:

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little


:

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

the two tables names are "1101" and "Master"
For table 1101 the fields are STATUS.
Then the fields where a "1" is present in the row for the field name are:
BAI, BAU, DAI, DAU, GTI, GTU, HPI, HPU, SPU, SPI.

For table Master the field I want in the results is entry status.

The users should match in the two tables according to ID.

The data types are all text.

You gotta have both table in the query and joined on the common field.
Post the table and field names with the field datatype.
--
KARL DEWEY
Build a little - Test a little

Ok I tried this in SQL. The first time I ran it says cannot find table. So I
changed YourTable to the name of one of the tables. The query ran but no
matter what I entered I never got results.

I'm using two tables. Table #1 has the information I'm searching for. I want
the field that I enter to have a "1" in the row. I want it to output certain
fields from both tables. I have it linked according to ID.

Do I need to establish more relationships with the two tables? Or what else
can I do to get this to work?

I appreciate the help!

Thanks!

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little


:

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks
:

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little


:

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

Let me rewrite what you posted so that you can confirm what I think your
tables look like.
Table 1101 ---
ID Status BAI BAU DAI DAU GTI GTU HPI HPU SPU SPI
X001 Open 1 1 1 1
Y092 Closed 1 1 1

Table Master ---
ID Entry_Status
X001 Open
Y092 Closed

Your orignal post said '...all of the ID's and Name's are in the resulting
query...' but your post of table and fields does not have Name as a field.

If I did not interpret your post correctly into the above then edit it so I
will understand.
--
KARL DEWEY
Build a little - Test a little

the two tables names are "1101" and "Master"
For table 1101 the fields are STATUS.
Then the fields where a "1" is present in the row for the field name are:
BAI, BAU, DAI, DAU, GTI, GTU, HPI, HPU, SPU, SPI.

For table Master the field I want in the results is entry status.

The users should match in the two tables according to ID.

The data types are all text.

You gotta have both table in the query and joined on the common field.
Post the table and field names with the field datatype.
--
KARL DEWEY
Build a little - Test a little

Ok I tried this in SQL. The first time I ran it says cannot find table. So I
changed YourTable to the name of one of the tables. The query ran but no
matter what I entered I never got results.

I'm using two tables. Table #1 has the information I'm searching for. I want
the field that I enter to have a "1" in the row. I want it to output certain
fields from both tables. I have it linked according to ID.

Do I need to establish more relationships with the two tables? Or what else
can I do to get this to work?

I appreciate the help!

Thanks!

:

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little


:

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks
:

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little


:

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

Yes that is correct except for Status.
In Table 1101 It says Sponsor/Re-sponsor

In Table Master it says Active/In Active

Thanks again.

I'd like for both to be in the results.

Let me rewrite what you posted so that you can confirm what I think your
tables look like.
Table 1101 ---
ID Status BAI BAU DAI DAU GTI GTU HPI HPU SPU SPI
X001 Open 1 1 1 1
Y092 Closed 1 1 1

Table Master ---
ID Entry_Status
X001 Open
Y092 Closed

Your orignal post said '...all of the ID's and Name's are in the resulting
query...' but your post of table and fields does not have Name as a field.

If I did not interpret your post correctly into the above then edit it so I
will understand.
--
KARL DEWEY
Build a little - Test a little

the two tables names are "1101" and "Master"
For table 1101 the fields are STATUS.
Then the fields where a "1" is present in the row for the field name are:
BAI, BAU, DAI, DAU, GTI, GTU, HPI, HPU, SPU, SPI.

For table Master the field I want in the results is entry status.

The users should match in the two tables according to ID.

The data types are all text.

You gotta have both table in the query and joined on the common field.
Post the table and field names with the field datatype.
--
KARL DEWEY
Build a little - Test a little


:

Ok I tried this in SQL. The first time I ran it says cannot find table. So I
changed YourTable to the name of one of the tables. The query ran but no
matter what I entered I never got results.

I'm using two tables. Table #1 has the information I'm searching for. I want
the field that I enter to have a "1" in the row. I want it to output certain
fields from both tables. I have it linked according to ID.

Do I need to establish more relationships with the two tables? Or what else
can I do to get this to work?

I appreciate the help!

Thanks!

:

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little


:

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks
:

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little


:

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks

 

[Guest]

Your second post said 'I would like to place the criteria on the ID.'
This query should do what you asked.
SELECT Master_.Status, [1101].*
FROM Master_ LEFT JOIN 1101 ON Master_.ID = [1101].ID
WHERE (((Master_.ID)=[Enter ID]));

It would have beeb a whole lot easier if you had given me your table
structure at the start.
--
KARL DEWEY
Build a little - Test a little

Yes that is correct except for Status.
In Table 1101 It says Sponsor/Re-sponsor

In Table Master it says Active/In Active

Thanks again.

I'd like for both to be in the results.

Let me rewrite what you posted so that you can confirm what I think your
tables look like.
Table 1101 ---
ID Status BAI BAU DAI DAU GTI GTU HPI HPU SPU SPI
X001 Open 1 1 1 1
Y092 Closed 1 1 1

Table Master ---
ID Entry_Status
X001 Open
Y092 Closed

Your orignal post said '...all of the ID's and Name's are in the resulting
query...' but your post of table and fields does not have Name as a field.

If I did not interpret your post correctly into the above then edit it so I
will understand.
--
KARL DEWEY
Build a little - Test a little

the two tables names are "1101" and "Master"
For table 1101 the fields are STATUS.
Then the fields where a "1" is present in the row for the field name are:
BAI, BAU, DAI, DAU, GTI, GTU, HPI, HPU, SPU, SPI.

For table Master the field I want in the results is entry status.

The users should match in the two tables according to ID.

The data types are all text.

:

You gotta have both table in the query and joined on the common field.
Post the table and field names with the field datatype.
--
KARL DEWEY
Build a little - Test a little


:

Ok I tried this in SQL. The first time I ran it says cannot find table. So I
changed YourTable to the name of one of the tables. The query ran but no
matter what I entered I never got results.

I'm using two tables. Table #1 has the information I'm searching for. I want
the field that I enter to have a "1" in the row. I want it to output certain
fields from both tables. I have it linked according to ID.

Do I need to establish more relationships with the two tables? Or what else
can I do to get this to work?

I appreciate the help!

Thanks!

:

What I posted is not just the criteria but the complete SQL for the query.
To use create a new query in design view and not select a table. Click on
menu VIEW - SQL View and paste the post in the window.
--
KARL DEWEY
Build a little - Test a little


:

I get an error message that says the syntax of the subquery in the expression
is incorrect. How do I copy and paste the below expression to try it out.
Also I put this in criteria for the id field correct?

Thanks
:

Try this --
SELECT [Enter ID] AS Selection, YourTable.*
FROM YourTable
WHERE YourTable.ID = [Enter ID];

--
KARL DEWEY
Build a little - Test a little


:

I would like to place the criteria on the ID.
As for try and fix the columns I meant that currently I have each
application listed as a column and users in rows (Users are listed by ID). If
that user is in that application, then the number one is marked. So to try
and change the columns into rows would be too time consuming to change the
setup of the table.

Thanks.



:

My problem is that all of the criteria I want to select is listed as a
field and there are over 500000 users to try and fix the columns.
I do not understand you problem.
What field will you place the criteria on?
What has 'over 500000 users' to do with criteria?
What does 'try and fix the columns' mean?
--
KARL DEWEY
Build a little - Test a little


:

I have two tables who have a relationship based on ID.
I want to make a query where the parameters for the criteria are asked and
then according to the selection, all of the ID's and Name's are in the
resulting query with the listed paramter (field) that was chosen.

My problem is that all of the criteria I want to select is listed as a field
and there are over 500000 users to try and fix the columns.

Thanks