Select Query Vs. Make-table Query

[Jenn]

When I run the query i'm working on as a select query it produces the table
that I am looking for, however it won't create/run it as a make-table query.
I need to create the table to use in a form and a report. I have checked my
reference books and I can't figure out why it will do one but not the other.

Please advise.

Thank you in advance for your assistance.

 

[Arvin Meyer [MVP]]

Try, creating a new Make-Table query using the first one as a source.
(Sometimes you need a bigger hammer)

 

[Jeff Boyce]

Jenn

If you wish to have a set of data as the source for a form or a report, a
SELECT query works quite well!

If you use a Make Table, won't you have duplicate data in your database?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Larson]

I'm with Jeff on this. Normally you can do everything you need with a
select query and don't need to make a separate table for it. It only really
makes sense to do it when you have a large amount of data and the Select
query takes a long time to run, so you populate a temp table (don't keep
creating a new one, but delete and append new records) and then you can use
for reports. But, you run the make table only once.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Jenn]

This is a large table, and it does not create duplicate. It is much easier
to have it as a make table, that way it runs quicker through the reports and
form, this wouldn't work as a History or append table because every year new
information is created in the tables. It's quite complex. I'm trying to
come up with a simpler way to complete it. I was just wondering as to what
would cause it to run as a select query and not as a make-table query. When
I run it as a make-table query, it gives me a records too large error. I
know the max number of bytes a table can contain is 2000 and i'm under that
limit.

I'll just keep on crunching a long with this one, Any advice would be great.

 

[Bob Larson]

I think you don't understand what I am saying. You do not need to make a
table to make it run quick through the reports. What you do is to create
the table ONE TIME and then you just clear out the table and then append new
records into it. That way you can also make sure that the datatypes are set
to a correct type (so that you don't end up with the errors you are
experiencing).

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[hor vannara]

I think you don't understand what I am saying. You do not need to make a
table to make it run quick through the reports. What you do is to create
the table ONE TIME and then you just clear out the table and then append
new records into it. That way you can also make sure that the datatypes
are set to a correct type (so that you don't end up with the errors you are
experiencing).

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

 

[Jeff Boyce]

Jenn

If you have fields in your table that COULD store more than that limit,
perhaps one of your records DOES hold more.

When you say "a large table", do you mean many columns or many records?

If you take existing data and use a Make Table query involving that existing
data, you are duplicating data.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jenn]

Jeff,

When all is said and done I should have 304 Columns and 934 rows. An
append query will not work with this as it would create duplicates. I
understand the statement of not creating another table, it's just easier to
create the table one time instead of running it each time I go into a form or
report.

I think I have everything figured out.

I was just curious as to why it would open the table through a select query
and not a make-table query.

Thank you everyone for your assistance with this.

 

[Jeff Boyce]

Jenn

A well-normalized Access table rarely has more than ... 30 fields?! I
suspect that your table is something that a spreadsheet would know and love,
but it probably causes both you and Access considerable headaches!

If you have 300+ columns (fields), and each one contained 10 bytes, you'd be
way over the limit per row/record.

Is there a reason you don't just keep this data in a spreadsheet? Perhaps
you are running into the issues you are seeing because you are trying to
feed Access (a relational database) 'sheet data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Larson]

I don't see how you expect to have 304 columns when the Access limit on
fields in a table is 255. Something has to give.

--

Thanks,

Bob Larson
Access MVP

Free Access Tutorials and Resources: http://www.btabdevelopment.com