can a query be converted to a table

[Guest]

I have 400 names in a table (7 fields)I made a query of 40 names from that
table. Can I now make a table from that query. I used the "create table"
wizzard but, can't do it. It looks like no relationship can be formed between
the query and the proposed new table. It seems like I should be able to
simply convert the query to a table. No such luck. Much appreciation for any
response.

 

[David Cox]

In the query design window there is a pull down option to turn it into a
Make Table query.

 

[Jeff Boyce]

Although a solution has been posted (see David's response), I have to ask
"why?"

If you can use a query to generate the 40 names that you need, why do you
need to duplicate data you already have in your database by creating a new
(duplicate) table? Is there a reason you can't just use the output of the
query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks David Thanks Jeff. I want to add some fields and you can't
add fields (columns)to a query. At least I wasn't able to.

 

[Jeff Boyce]

Bob

How do you mean "add fields to a query". I do it all the time. For
example, in a query design mode, in an empty "field" column, I add:
NewField: Date()

When I run this query, it displays today's date along with all the other
fields I selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff. That helps. Ill explain furthur though. I have the 40 names in
the query. I need to have my first field be a number 1 to 40. I put the
numbers in manually 1 to 40 in that column.
How do I get that column to update the numbers when a row is deleted
or as a new row is added? If I let access do auto number, then the number
attaches to that row and can not be broken from that row. I should have
explained earlier more specifically what I was trying to do. Thank you for
your help so far. I am learning as I go here. Bob Levin

 

[Guest]

Jeff: I followed your example. In query design mode, in an empty "field"
column I typed in a field name. I called it "NUM" for number. I ran the query
and the field creates it's own name, expr 1, or something like that. Do you
know how to name the field with a name of your choice? I think I am making
this more complicted than it really is. could you simplify it when you get
the time. thanks much

 

[John Vinson]

Jeff: I followed your example. In query design mode, in an empty "field"
column I typed in a field name. I called it "NUM" for number. I ran the query
and the field creates it's own name, expr 1, or something like that. Do you
know how to name the field with a name of your choice? I think I am making
this more complicted than it really is. could you simplify it when you get
the time. thanks much

I'm sure Jeff would find it very helpful if you would open your query
in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[Guest]

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category, [Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.
Bob Levin

 

[Jeff Boyce]

Bob

In query design mode (or in SQL view), highlight the "Expr1" and type what
you want instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category,
[Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.
Bob Levin

I'm sure Jeff would find it very helpful if you would open your query
in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[Jeff Boyce]

Bob

(not sure my last response made it through)

In query design mode (or in SQL view), highlight "Expr1" and type what you
want it to be.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category,
[Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.
Bob Levin

I'm sure Jeff would find it very helpful if you would open your query
in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[Guest]

Jeff : or Others: Sorry, It still doesn't work. I highlite the "Expr 1"
and type the field name I choose. When I click run, a window tells me to
"Enter Parameter Value". I click OK, and the field name changes back to Expr
1. can you give furthur tip ? Thanks so much for yours and others help.

Bob Levin

Bob

(not sure my last response made it through)

In query design mode (or in SQL view), highlight "Expr1" and type what you
want it to be.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category,
[Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.
Bob Levin

On Sun, 3 Sep 2006 21:08:03 -0700, Bob Levin

Jeff: I followed your example. In query design mode, in an empty
"field"
column I typed in a field name. I called it "NUM" for number. I ran the
query
and the field creates it's own name, expr 1, or something like that. Do
you
know how to name the field with a name of your choice? I think I am
making
this more complicted than it really is. could you simplify it when you
get
the time. thanks much

I'm sure Jeff would find it very helpful if you would open your query
in SQL view and post the SQL text here.

John W. Vinson[MVP]

 

[John Vinson]

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category, [Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.

This assumes that there is a field named [Number] in your table - or
else it will prompt with Number as a parameter.

What are you expecting Number to do???

John W. Vinson[MVP]

 

[Guest]

Thank You John and Jeff: I got it straight in my head now. I didn't
understand that a query only displays info that comes from a table, so the
field name has to be in my table before I can add it to my query. Dah!!!
--
Bob Levin

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category, [Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.

This assumes that there is a field named [Number] in your table - or
else it will prompt with Number as a parameter.

What are you expecting Number to do???

John W. Vinson[MVP]

 

[Jeff Boyce]

Bob

Strictly speaking, no. A query can display any "field" you add to it, but
if you are looking for a field that comes from a table, that table has to be
part of the query.

So I can add a field that contains nothing but "blah, blah, blah" to a query
and have that displayed in every row returned by the query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank You John and Jeff: I got it straight in my head now. I didn't
understand that a query only displays info that comes from a table, so the
field name has to be in my table before I can add it to my query. Dah!!!
--
Bob Levin

Here is the SQL view of my query.
-- SELECT Contacts.LastName, Contacts.FirstName, Contacts.Address,
Contacts.City, Contacts.St, Contacts.Zip, Contacts.Phone, Contacts.Cell,
Contacts.DOB, Contacts.Div, Contacts.DateJoined, Contacts.Category,
[Number]
AS Expr1
FROM Contacts
WHERE (((Contacts.Category)="SGI Sunrise District"));
Hope this helps. Thanks much.

This assumes that there is a field named [Number] in your table - or
else it will prompt with Number as a parameter.

What are you expecting Number to do???

John W. Vinson[MVP]

 

[John Vinson]

Thank You John and Jeff: I got it straight in my head now. I didn't
understand that a query only displays info that comes from a table, so the
field name has to be in my table before I can add it to my query. Dah!!!

Well... not really. A field in a Query can be a field from one of the
tables in that query's FROM clause, or it can be a calculated
expression using one or more fields from the query's tables, or it can
be a calculated expression which doesn't reference any field at all.
If you put

Number: 42

in a vacant Field cell, then every record in that query will have 42
in that field, regardless of what other fields or tables are involved.

John W. Vinson[MVP]