Create a New Table with new fields using a Query.

[penmetsa]

I have a problem.

I have a query in my database.
I have to create a new table with new fields (not the fields in the query).
This new table is like a summary table of the query. How do we create a new table with new fields?

And can we "count" the number of records in the query and display the number in the new table under a new field using any worksheet expression?

And can this table be brought out as a fine report?

Please help me with the procedure or any code required?

Penmetsa

 

[aleb]

What you can do is:
Create new "make table query"
To create a blank column in a table using make table query. You need to define this field like this
MYFIELD:[]

Of course the name"myfield" you will replace with the name of the column

Now when you run this query it will ask you for the value for that new column, just click ok . if you will put any value access will populate the new column with the value you provide.

 

[meboz]

MYFIELD: Count(newtable.somefield)

 

[penmetsa]

Hi alexie,

What if I want to enter values into the new table.

The code below is what I wrote in SQL view of the query.

CREATE TABLE tblTripgeneration ([Destination_Purpose] TEXT(10) NULL, Number_of_Trips INTEGER NULL, Trip_Percentage INTEGER NULL)

inth = DCount("[PURPOSE]","trips","Purpose = 1,2")
intw = DCount("[PURPOSE]","trips","Purpose = 3")

INSERT INTO tblTripgeneration ([Destination_Purpose],Number_of_Trips,Trip_Percentage) VALUES('Home',inth,inth)

Can u please help me with the code.

Thanks
Penmetsa