Calculating query - results go to table too

[Beginner]

Hi!

I'm calculating values to a query with VBA. When I do the calculation the
results also go into the table which I use as a base for my query. Why? I
don't really mind, because I intend to use the query in the next step of my
project, not the table, but should I be concerned that the values go into the
table as well?

My table is sorted ascending (id-field), but I was told that by making a
query I could be sure that the data is really in the ascending order. The
fields are the same in the query and in the table.

I would also like to know is it possible to add fields into a query with
VBA? I think it is but I don't know how. That way I wouldn't have the same
fields in the query and in the table, the table would contain only the fields
that have the data I need to calculate.

Thanks for answers beforehand!

- Beginner -

 

[Alex Dybenko]

Hi,
you can change query SQL - add/delete fields it returns, change order, etc:

currentdb.querydefs("MyQuery").SQL="Select field1 from table1 ..."

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Beginner]

Hmm.. I'm not sure if I understand.. I know that I can select fields from
tables to my query, but can I add fields that aren't in tables? Like I can
add fields to a table with SQL: ALTER TABLE table ADD.. Completely new
fields.

-Beginner-

 

[Alex Dybenko]

Hi,
in this case you have to modify the whole sql. Say you had query based on
one table:
currentdb.querydefs("MyQuery").SQL="Select field1 from table1"

now you need to add field from table2, of course - you have to join these
tables:

currentdb.querydefs("MyQuery").SQL="Select table1.field1, table2.field2 from
table1 Inner join table2 on..."


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Beginner]

Hi!

I'm afraid I didn't mean that I would want to take a field from another
table. I ment that can I add completely new fields that aren't in any tables?
For example, I have only one table with fields Name and Country. Can I make a
query that has these fields plus a field called Foreign, that states if a
person is foreign or not. This is an imaginary example, but I hope you
understand what I mean. The Foreign field isn't in any table..

Sorry that I'm so bad at explaining this!

- Beginner -

 

[John W. Vinson]

Hi!

I'm afraid I didn't mean that I would want to take a field from another
table. I ment that can I add completely new fields that aren't in any tables?
For example, I have only one table with fields Name and Country. Can I make a
query that has these fields plus a field called Foreign, that states if a
person is foreign or not. This is an imaginary example, but I hope you
understand what I mean. The Foreign field isn't in any table..

Yes; you can create multiple calculated fields in a query, by typing a new
fieldname followed by a colon and the expression which gives the new field
value, in a vacant field cell in the query grid. E.g.

Foreign: [Country] <> "United States of America"

or

Profit: [Income] - [Loss]


John W. Vinson [MVP]

 

[Beginner]

Okay, thanks! Just being curious, how to do this in code? I know I don't need
to do it in code, since making it in query window is so simple and easy, but
I'd still like to learn new stuff in coding department also

-Beginner-

Hi!

I'm afraid I didn't mean that I would want to take a field from another
table. I ment that can I add completely new fields that aren't in any tables?
For example, I have only one table with fields Name and Country. Can I make a
query that has these fields plus a field called Foreign, that states if a
person is foreign or not. This is an imaginary example, but I hope you
understand what I mean. The Foreign field isn't in any table..

Yes; you can create multiple calculated fields in a query, by typing a new
fieldname followed by a colon and the expression which gives the new field
value, in a vacant field cell in the query grid. E.g.

Foreign: [Country] <> "United States of America"

or

Profit: [Income] - [Loss]


John W. Vinson [MVP]

 

[John W. Vinson]

Okay, thanks! Just being curious, how to do this in code? I know I don't need
to do it in code, since making it in query window is so simple and easy, but
I'd still like to learn new stuff in coding department also

You can create a query in code by constructing a SQL string and using the
CreateQuerydef method. If you want a query with a calculated field, then you
need to create a query somehow - in the user interface or in code.

John W. Vinson [MVP]

 

[Beginner]

Okay, I'll take a closer look on the querydef -method. Thaks for the answers

-Beginner-