cross-table query not shown correctly in table from make-table query (1 Viewer)

killerflappy · Oct 6, 2017

I’m a beginner in making Query’s (and SQL).

In the Example file there is a cross table query (qryCountPerStore) with the information I need.
I want this information in a table. I do this with a make table query (Kopie van qryCountPerStore).
The table thas is made (tblqryCountPerSore) is not shown correctly. I see the products (A, B and C) are counted per row per store. I want the table to show the count of A, B and C on 1 row. Just like the qryCountPerStore.
So how can I get a table with the result of the qryCountPerStore?

plog · Oct 6, 2017

Then make a MAKE query out of qryCountPerStore.

However, why must this data be a table? Why not just use qryCountPerStore?

static · Oct 6, 2017

A query can't be 2 things at once.
It can either be a crosstab or a maketable.

You can use one query inside of another (different type of) query though.

select * into newtable from qrycountperstore

killerflappy · Oct 6, 2017

plog said:
However, why must this data be a table? Why not just use qryCountPerStore?

I copied the query and convert it to a make table. Then I convert it to a make table. So I have to test if a new make table does the trick. Thanks.

I need to change some data by human interaction.

Pat Hartman · Oct 6, 2017

FYI, when you run a make table query, Access does NOT reuse the space occupied by the existing data. Access allocates entirely new space. The same situation occurs when you use delete and append queries to replace the contents of entire tables. Both techniques cause database bloat and you will need to increase the frequency with which you compact the database or run the risk of exceeding the 2G limit for Access applications.

As someone already mentioned, it is almost always better to use the select query directly as the RecordSource for your form or report rather than saving the data permanently.

killerflappy · Oct 10, 2017

static said:
A query can't be 2 things at once.
It can either be a crosstab or a maketable.

You can use one query inside of another (different type of) query though.

select * into newtable from qrycountperstore

Yes! this does the trick. Thanks quys!

cross-table query not shown correctly in table from make-table query (1 Viewer)

killerflappy

Registered User.

Attachments

plog

Banishment Pending

static

Registered User.

killerflappy

Registered User.

Pat Hartman

Super Moderator

killerflappy

Registered User.

Similar threads

Users who are viewing this thread