By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Looking for something like a "Sub-Table"

blyxx86
100+
P: 256
Hey everyone, I'm working on a new database system to generate reports on the work technicians are doing and what their overall Pass/Fail rate is. I have the majority of the database set up and working. However, I have a couple reports that are generated from a table (which is generated by a Make Table Query)

Now, is there a way to do the same thing as a "Make Table Query" with something like a sub-table, or a table that would run in the background of the form I use to filter the information from one main database.

I have fields like "ID, Date, Overall, Tech, Description, Failure, Fix"

Also, the one thing I don't like about the "Make Table Query" is when it is ran with a form button, I have to open the query, close the query, and then it asks if I want to delete the previous table. I've set up a macro to bypass all of that, but there must be a better way.

I am using Access 2000.
Nov 20 '06 #1
Share this Question
Share on Google+
23 Replies


blyxx86
100+
P: 256
Also, I have a general question about databases. I've only been using them for about a month with no prior experience using them, just some overall knowledge and a bit of experimentation.

Is it a good idea for me to have my main database be filled with almost entirely numbers... where a column like Model would be filled with a number like 00034, but when I ran a report how would I make the report show up the model name instead of the number. I've figured that out for Pass/Fail (IIF[field]=-1,"Pass","Fail") and this is ran during queries and reports. However, I am unsure if this is a good idea or not.
Nov 20 '06 #2

nico5038
Expert 2.5K+
P: 3,072
Why do you use a make-table query ?
Just change the make-table query into a SELECT query and use that instead.

Only when the creation takes a lot of time and when the data is used for multiple reports we revert to using a temp table.

When the temp table is needed first create a query with this SQL:

DROP TABLE <your "create-table" name>;

Run this before activating the make-table query. Behind your button repeat the present statement and add the "drop table" query.

Finally, it's OK to use an IIF() statement. Yours can even be shortened into:
IIF([Field],"True","False")

Nic;o)
Nov 20 '06 #3

ADezii
Expert 5K+
P: 8,638
Hey everyone, I'm working on a new database system to generate reports on the work technicians are doing and what their overall Pass/Fail rate is. I have the majority of the database set up and working. However, I have a couple reports that are generated from a table (which is generated by a Make Table Query)

Now, is there a way to do the same thing as a "Make Table Query" with something like a sub-table, or a table that would run in the background of the form I use to filter the information from one main database.

I have fields like "ID, Date, Overall, Tech, Description, Failure, Fix"

Also, the one thing I don't like about the "Make Table Query" is when it is ran with a form button, I have to open the query, close the query, and then it asks if I want to delete the previous table. I've set up a macro to bypass all of that, but there must be a better way.

I am using Access 2000.
'To eliminate the Prompts involved with a Make Table Query (i.e. the previously created Table), you can use the following code snippet

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2.     'Run the Make Table Query
  3. DoCmd.SetWarnings True
Nov 20 '06 #4

NeoPa
Expert Mod 15k+
P: 31,494
That's some good thinking.
Go to the stickies at the top of the Access forum for links that will help you get up-and-running.

You can use filters in a report directly, or a query based on the table which is filtered (has a WHERE clause).
If you use an APPEND query rather than a Make Table query, you can design the table exactly as you want it, then use a DELETE query (DELETE * FROM [YourTable]) to clear it down before use.
Nov 20 '06 #5

blyxx86
100+
P: 256
I can't use a select query because it runs into issues because I have an external spreadsheet that gets information and it can not seem to access any values from the query (whether or not it is static or dynamic values.)

I do use multiple reports, with different query results based on a certain date range... but I am not sure if I should even use a temp table because of the external access needed by my one spreadsheet. Would that temp table be accessed by an external source when I no longer have the database open?

so I would type.. DROP TABLE [TempTableName] for the SQL of a query?

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![MultiForm]![BegDate] DateTime, [Forms]![MultiForm]![EndDate] DateTime;
  2. SELECT QC.ID, QC.Date, QC.Overall, QC.Tech, QC.Model, QC.Serial, QC.Customer, QC.Scanner, QC.Display, QC.Keypad, QC.Radio, QC.Structural, QC.Description, QC.Failure, QC.Fix INTO [Date Query]
  3. FROM QC
  4. WHERE (((QC.Date) Between [Forms]![MultiForm]![BegDate] And [Forms]![MultiForm]![EndDate]) AND ((QC.Overall)<>"Pass"))
  5. ORDER BY QC.ID, QC.Date;
  6.  
Nov 20 '06 #6

ADezii
Expert 5K+
P: 8,638
Also, I have a general question about databases. I've only been using them for about a month with no prior experience using them, just some overall knowledge and a bit of experimentation.

Is it a good idea for me to have my main database be filled with almost entirely numbers... where a column like Model would be filled with a number like 00034, but when I ran a report how would I make the report show up the model name instead of the number. I've figured that out for Pass/Fail (IIF[field]=-1,"Pass","Fail") and this is ran during queries and reports. However, I am unsure if this is a good idea or not.
The IIf(expr, truepart, falsepart) Function is extremely inefficient and should not
be used except as a last resort:
1) It is very slow in processing
2) Both the Truepart and Falsepart are "always" evaluated even if it returns only 1 of them
3) Undesireable results can occur such as in the case where the Falsepart results in a division by 0, an Error will occur even if expr is True
Nov 20 '06 #7

blyxx86
100+
P: 256
'To eliminate the Prompts involved with a Make Table Query (i.e. the previously created Table), you can use the following code snippet

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2.     'Run the Make Table Query
  3. DoCmd.SetWarnings True
How do I run the query from VB? Would it be...
Expand|Select|Wrap|Line Numbers
  1. ]DoCmd.SetWarnings False
  2. DoCmb.OpenQuery(Date Query)   'Run the Make Table Query
  3. DoCmd.SetWarnings True
Nov 20 '06 #8

blyxx86
100+
P: 256
How do I run the query from VB? Would it be...
Expand|Select|Wrap|Line Numbers
  1. ]DoCmd.SetWarnings False
  2. DoCmb.OpenQuery(Date Query)   'Run the Make Table Query
  3. DoCmd.SetWarnings True

I tried that, but I believe there is a problem with the name of my query having spaces... How do I type a space into visual basic?

Something like "& Date Query &" or something... right?
Nov 20 '06 #9

nico5038
Expert 2.5K+
P: 3,072
You'll need two queries:
1) qryDeleteQC with:
DROP TABLE QC
2) your maketable query

These need to be executed in this sequence to suppress the error message.
You can do that in code behind the button by using:

docmd.RunSQL "qryDeleteQC"
docmd.RunSQL "name of your maketable query"

Getting the idea ?

Nic;o)
Nov 20 '06 #10

blyxx86
100+
P: 256
You'll need two queries:
1) qryDeleteQC with:
DROP TABLE QC
2) your maketable query

These need to be executed in this sequence to suppress the error message.
You can do that in code behind the button by using:

docmd.RunSQL "qryDeleteQC"
docmd.RunSQL "name of your maketable query"

Getting the idea ?

Nic;o)
So.. what would my "qryDeleteQC" be like? It would be a Delete Query, would there be any special where statements within the query? I'm guessing no.

Also, when I do the maketable query, couldn't I just switch to an append query and append it to the (now empty) table.

so...
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.RunSQL "qryDeleteQC"
  3. DoCmd.RunSQL "qryAppendQC" `Or qryTableQC
  4. DoCmd.SetWarnings True
  5.  
Nov 20 '06 #11

blyxx86
100+
P: 256
Ok... came up with a couple errors..

Expand|Select|Wrap|Line Numbers
  1. DoCmd.runSQL (DELETE [DateQuery].[ID], [DateQuery].[Date], [DateQuery].[Overall], [DateQuery].[Tech], [DateQuery].[Model], [DateQuery].[Serial], [DateQuery].[Customer], [DateQuery].[Scanner], [DateQuery].[Display], [DateQuery].[Keypad], [DateQuery].[Radio], [DateQuery].[Structural], [DateQuery].[Description], [DateQuery].[Failure], [DateQuery].[Fix]
  2. FROM DateQuery;)
  3.  
after the first [DateQuery] I get an "Expected: list separator or )" error.

Do I have to clear out all the brackets?
Nov 20 '06 #12

NeoPa
Expert Mod 15k+
P: 31,494
If you choose the 'Make Table' route then the delete query would be of the form
Expand|Select|Wrap|Line Numbers
  1. DROP [TableName]
If you choose the APPEND route then the delete query would be of the form
Expand|Select|Wrap|Line Numbers
  1. DELETE * FROM [TableName]
Nov 20 '06 #13

NeoPa
Expert Mod 15k+
P: 31,494
Ok... came up with a couple errors..

Expand|Select|Wrap|Line Numbers
  1. DoCmd.runSQL (DELETE [DateQuery].[ID], [DateQuery].[Date], [DateQuery].[Overall], [DateQuery].[Tech], [DateQuery].[Model], [DateQuery].[Serial], [DateQuery].[Customer], [DateQuery].[Scanner], [DateQuery].[Display], [DateQuery].[Keypad], [DateQuery].[Radio], [DateQuery].[Structural], [DateQuery].[Description], [DateQuery].[Failure], [DateQuery].[Fix]
  2. FROM DateQuery;)
  3.  
after the first [DateQuery] I get an "Expected: list separator or )" error.

Do I have to clear out all the brackets?
DoCmd.RunSQL expects a single string parameter (there is another optional parameter we'll ignore here).
Your code has not formatted it as a string, but as lots more code.
You need (")s around the string you're passing.
Nov 20 '06 #14

blyxx86
100+
P: 256
DoCmd.RunSQL expects a single string parameter (there is another optional parameter we'll ignore here).
Your code has not formatted it as a string, but as lots more code.
You need (")s around the string you're passing.
How do I write it as string? Just a simple example of what to write.. like..

DELETE * FROM DateQuery

INSERT INTO DateQuery ( ID, Date......)

I'm thinking of just using..
DoCmd.OpenQuery "qryDeleteDateQuery", ,acReadOnly
DoCmd.OpenQuery "qryAppendDateQuery", ,acReadOnly

because the SQL for my append query is thus:
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![MultiForm]![BegDate] DateTime, [Forms]![MultiForm]![EndDate] DateTime;
  2. INSERT INTO DateQuery ( ID, [Date], Overall, Tech, Model, Serial, Customer, Scanner, Display, Keypad, Radio, Structural, Description, Failure, Fix )
  3. SELECT QC.ID, QC.Date, QC.Overall, QC.Tech, QC.Model, QC.Serial, QC.Customer, QC.Scanner, QC.Display, QC.Keypad, QC.Radio, QC.Structural, QC.Description, QC.Failure, QC.Fix
  4. FROM QC
  5. WHERE (((QC.Date) Between [Forms]![MultiForm]![BegDate] And [Forms]![MultiForm]![EndDate]))
  6. ORDER BY QC.ID, QC.Date;
  7.  
and that is confusing to me... i dont know how to write it as a string.
Nov 20 '06 #15

nico5038
Expert 2.5K+
P: 3,072
When you're table does contain a lot of rows it's best to use the "DROP TABLE" as that's executed on "table level". The "Delete * from tblX" statement will delete row by row and also use a logfile slowing it further down.

The DROP TABLE is created by:
1) Create a new query, but don't select a table from the popup menu
2) Press the left most button reading "SQL"
3) Type the command like:
DROP TABLE QC
4) Save the query and give it the name "qryDropQC"

The Maketable query can be left "as is"

Now go to the button with the execution of the MakeTable query and add the line for the DROP TABLE query.

Need more info?

Nic;o)
Nov 20 '06 #16

NeoPa
Expert Mod 15k+
P: 31,494
Expand|Select|Wrap|Line Numbers
  1. DoCmd.runSQL "DELETE [DateQuery].[ID], [DateQuery].[Date], [DateQuery].[Overall], [DateQuery].[Tech], [DateQuery].[Model], [DateQuery].[Serial], [DateQuery].[Customer], [DateQuery].[Scanner], [DateQuery].[Display], [DateQuery].[Keypad], [DateQuery].[Radio], [DateQuery].[Structural], [DateQuery].[Description], [DateQuery].[Failure], [DateQuery].[Fix]
  2. FROM DateQuery;"
My reply did contain instructions for that - but I also included the explanation in case you were interested in understanding why.
Nov 20 '06 #17

NeoPa
Expert Mod 15k+
P: 31,494
Nico,

I wouldn't argue technically with what you say.
But sometimes the benefit of having a pre-formatted table can more than offset the performance penalty.
It depends on various factors.

-Adrian.
Nov 20 '06 #18

nico5038
Expert 2.5K+
P: 3,072
True Adrian, but as the MakeTable query did work for the questioner I proposed the DROP TABLE as a fast and secure solution.
Having needs for specific datatypes in the table fields will indeed require an append query and a slower "delete * from QC" solution.
However, both the "DROP TABLE QC" and the "Delete * from QC" are considered a better alternative as the "docmd.setwarnings false" command as that's not only affecting the query's message, but also all of the Access system messages when the query runs into an error and abbreviates the code....

Nic;o)
Nov 20 '06 #19

blyxx86
100+
P: 256
True Adrian, but as the MakeTable query did work for the questioner I proposed the DROP TABLE as a fast and secure solution.
Having needs for specific datatypes in the table fields will indeed require an append query and a slower "delete * from QC" solution.
However, both the "DROP TABLE QC" and the "Delete * from QC" are considered a better alternative as the "docmd.setwarnings false" command as that's not only affecting the query's message, but also all of the Access system messages when the query runs into an error and abbreviates the code....

Nic;o)
Is there a better way than to Append, I think I remember seeing an UPDATE command.. Appending is rather slow, as I have about 2000 results to return from the query. But it is a daily update that is being done. So it would remove things not within my date range, and add things that are now in the date range.

I am loving how much I'm learning though, I'm enjoying it.
Nov 20 '06 #20

NeoPa
Expert Mod 15k+
P: 31,494
UPDATE changes the values of fields in existing records.
I don't think this is what you're after.

Your options are APPEND & SELECT INTO (Make Table) I think.
Nov 20 '06 #21

blyxx86
100+
P: 256
UPDATE changes the values of fields in existing records.
I don't think this is what you're after.

Your options are APPEND & SELECT INTO (Make Table) I think.
It just seems a bit redundant to clear an entire table with some 2000 fields, to enter 1500 of them again. But maybe that's just how I have it set up. Perhaps I should filter an entire table based on what the values in the fields are, and not create a table based on the value of the fields. Hmmm, there's a concept.
Nov 20 '06 #22

NeoPa
Expert Mod 15k+
P: 31,494
One benefit of DELETE over DROP TABLE, is that you can clear records selectively.
If you have a date field (called for instance datEntered) you can have something like
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM [YourTable]
  3. WHERE (datEntered>=Date())
I don't know which selection criteria you need so this is just explanatory code.

You're probably better off with queries rather than tables anyway though - as suggested above.
Nov 20 '06 #23

blyxx86
100+
P: 256
One benefit of DELETE over DROP TABLE, is that you can clear records selectively.
If you have a date field (called for instance datEntered) you can have something like
Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM [YourTable]
  3. WHERE (datEntered>=Date())
I don't know which selection criteria you need so this is just explanatory code.

You're probably better off with queries rather than tables anyway though - as suggested above.
I guess it all comes down to what I'm using the database for. Having a seperate table generated once per day, that things are pulled from, or having something query a much larger table 100 times per day. I'm not sure which is better right now, but we shall see.

You guys rock!
Nov 21 '06 #24

Post your reply

Sign in to post your reply or Sign up for a free account.