I have a make table query that is referencing many tables. When I hit run, it acts like its running but never stops, throws an error code, or creates a table. Any suggestions? Ive tried changing it to a simple query as well and get the same results.
Make table query wont run!!!
- Thread starter TEllison
- Start date
dont have the code. Im not familiar with code writing, its all been done through wizards. The query is referencing 20+ tables which are fed by MAKE TABLE queries. The MAKE TABLE queries are run through a macro. This part is working as expected. But when I hit "RUN" on my make table query that brings everything together as one, it acts as if its running but never actually finishes the command and the only way to do anything else to hit ESC and kill the command. No error messages or anything, just dead space with nothing actually happening. Ive allowed it to run for 20+ minutes and nothing happens.
BlueIshDan
☠
- Local time
- Today, 15:39
- Joined
- May 15, 2014
- Messages
- 1,122
Tell me, is this for work or for school?
heres a copy. the query that isnt working is "Completion Query". Again Im not an access guru or anything, Ive self taught myself as Ive went whiling building this database. Its my first one, so Im sure there are some things I could have done differently. Thanks in advance for the help
Your query has 16 tables and no join fields. What you have is called a cartesian product... Basically every table is trying to reference every field against all the other tables...
You need to read up a little bit about how tables link together in a relational database.
When you run the query it's trying to link all the fields in every table... So basically it's running... it's just taking a LONG time... It might run in full if you let it, or more likely you'll run out of memory before it's complete... whatever the case, I doubt you'll be getting the data from it, you hope to.
You need to figure out your field links first. Also you don't need that many tables... try to consolidate all those tables that have the same fields in them (like your score tables) and add an extra field to identify the type (e.g. TB1-2N, TB1-2S etc.).
GL
You need to read up a little bit about how tables link together in a relational database.
When you run the query it's trying to link all the fields in every table... So basically it's running... it's just taking a LONG time... It might run in full if you let it, or more likely you'll run out of memory before it's complete... whatever the case, I doubt you'll be getting the data from it, you hope to.
You need to figure out your field links first. Also you don't need that many tables... try to consolidate all those tables that have the same fields in them (like your score tables) and add an extra field to identify the type (e.g. TB1-2N, TB1-2S etc.).
GL
@ gblack thanks for the help. Ill read up on that. The tables are all set up for seperate areas of the facility I work at. Ill look into consolidating and possibly using a query to seperate the data I need? Is this the best route to take, or should I do something else?
- Local time
- Today, 19:39
- Joined
- Sep 12, 2006
- Messages
- 15,987
what are you trying to do, in general
access is just not like excel. you can do most things in excel without needing code/macros. you cannot in access. if you are new to access, you need to spend some/a lot of time learning first. Is this is important for your firm, get some help in.
access is just not like excel. you can do most things in excel without needing code/macros. you cannot in access. if you are new to access, you need to spend some/a lot of time learning first. Is this is important for your firm, get some help in.
@gblack Ive tried establishing relationships and have had no change in the results in how this thing runs. Im needing to reference the same field from each table, so Ive assumed an inner join would work but hasnt? I have no clue where to go from here???
GinaWhipp
AWF VIP
- Local time
- Today, 14:39
- Joined
- Jun 21, 2011
- Messages
- 5,899
Hmm, biggest problem is the table set up, it needs to be adjusted. You have all those tables with the same fields but different Score Types, not seeing how you could ever join them. What is should be is...
tblScores
sScoreID
sScoreTypeID (FK - relate to tblScoreTypes)
sDate
etc...
tblScoreTypes
stScoreTypeID (PK)
stScoreType (AAM, Apex Tuber, etc...)
To answer your question, in your present set up you're going to have to first create a UNION query to normalize the tables and then run a CROSSTAB query to get the presentation you want. This will still be slow but it will finish.
Side note: Either way there is no need to make a table the query will give you the view you want everytime it's run. If you want you can then export to Excel or present in a Report.
tblScores
sScoreID
sScoreTypeID (FK - relate to tblScoreTypes)
sDate
etc...
tblScoreTypes
stScoreTypeID (PK)
stScoreType (AAM, Apex Tuber, etc...)
To answer your question, in your present set up you're going to have to first create a UNION query to normalize the tables and then run a CROSSTAB query to get the presentation you want. This will still be slow but it will finish.
Side note: Either way there is no need to make a table the query will give you the view you want everytime it's run. If you want you can then export to Excel or present in a Report.
