Make Table Query and setting Number type

[Jasonm]

Hi,

I am using the following SQL to create a table with two newly created
fields:

SELECT Equipment.ID, Equipment.CategoryID, Equipment.HourReading,
Max(Hours.Hours) AS MaxOfHours, 0 AS NewHours, Equipment.Description, Date()
AS NewDate INTO tblHoursEntryPS
FROM Equipment INNER JOIN Hours ON Equipment.ID = Hours.EquipmentID
GROUP BY Equipment.ID, Equipment.CategoryID, Equipment.HourReading, 0,
Equipment.Description, Date()
HAVING (((Equipment.CategoryID)=7) AND ((Equipment.HourReading)=True));


I would like the field NewHours to have a format of double with two
decimals. Is there a way that i can easily so this by setting the properties
of the new field in the statement 0 AS NewHours...

Any assistance would be appreciated.

Jason M Canady

 

[John Vinson]

I am using the following SQL to create a table with two newly created
fields:

MakeTable queries are VERY rarely needed. Why do you feel that you
need to do so? A Form, a Report, an export, even another query can be
based on a Select Query directly; the extra step of a make-table query
just adds time to the execution and bloat to your database!

If you do need this level of control, I'd suggest having
tblHoursEntryPS as a permanently stored table in your database (with
whatever field types you like); rather than deleting and recreating
the table, simply run a Delete query to empty all its records and
change your current query to an Append query instead of a MakeTable.
You'll still need to regularly Compact your database.

John W. Vinson[MVP]

 

[Jasonm]

John,

Thanks for the reply...

In trying to make my db easier for my users I am making things harder on
myself... I am trying to propagate a table with a list of all of the
equipment that requires an hour reading so that the user enters in all of
the data for the week and then an update query runs that appends these "new"
records to a table that stores all of the hour readings... (I'm not sure if
I have explained that well enough, but I gave it a stab!)

What I am doing right now is that I have a set table that the user enters
the data into and then the above append operation is done. What I am trying
to add is the ability to validata the numbers that the users are entering
into the db. I thought that the easiest way to do this would be to recreate
the entry table each time with a query that would give me the last entry
from the table that stores all of the results thus giving me something to
compare to. I tried validating by linking directly to the other table
(tblHours) but could not get it to work...

The short of it is I need a way to prevent an entry from going into tblHours
that is less than the last entry for the piece of equipment. I am thinking
that I may have to work on doing this through vba, I was just trying to
avoid that as my vba isn't the greatest and it would take quite a while for
me to work out...

Again, thanks for the input...
I'll keep working on a solution. If you think of something that would work,
please post it here!

Jason M Canady

 

[John Vinson]

John,

Thanks for the reply...

In trying to make my db easier for my users I am making things harder on
myself... I am trying to propagate a table with a list of all of the
equipment that requires an hour reading so that the user enters in all of
the data for the week and then an update query runs that appends these "new"
records to a table that stores all of the hour readings... (I'm not sure if
I have explained that well enough, but I gave it a stab!)

hmmm... sounds like there could be some redundancy going on here. And
I don't see why this should require any MakeTable queries (other than
once and once only); if you're appending the same set of data weekly,
you'ld do best to have a "template" table of the week's data, and
append it weekly. I guess I'm not visualizing your process correctly;
you say "an update query that appends..." which is a contradiction
(update queries update existing records).

What I am doing right now is that I have a set table that the user enters
the data into and then the above append operation is done. What I am trying
to add is the ability to validata the numbers that the users are entering
into the db.

Well, that's the hard way to do it for sure! Hopefully you're not
using table datasheets for data entry or editing...!? That should
NEVER be required.

Much better would be to give the user a Form, and validate the data
right there on the Form. It would be bound to the table where the data
is to end up, but the validation would be done *on the form itself*,
before the record is loaded to the table.

I may be wrong here if the validtion involves counts or sums over
multiple records - but I still see no benefit to creating new tables.

I thought that the easiest way to do this would be to recreate
the entry table each time with a query that would give me the last entry
from the table that stores all of the results thus giving me something to
compare to. I tried validating by linking directly to the other table
(tblHours) but could not get it to work...

Since I have NO trace of a notion what sort of validation you're doing
I cannot contribute much here, other than to say that it is absolutely
*NOT* necessary - in fact it's a Very Bad Idea - to create a new table
just in order to visually see data from an existing table!! Just
display it on a Form; if you don't want that record being edited, set
the properties of the form or the controls on the form to lock them.

The short of it is I need a way to prevent an entry from going into tblHours
that is less than the last entry for the piece of equipment. I am thinking
that I may have to work on doing this through vba, I was just trying to
avoid that as my vba isn't the greatest and it would take quite a while for
me to work out...

If you could explain (using table and field names and example data)
what's valid and what's not, we'll be glad to help. VBA will be the
simplest way to do this, but it's easy VBA (or may be easy, depending
on what validation requirements you specify!)

John W. Vinson[MVP]

 

[Jasonm]

John, Thanks again for the info...
I have found a way to do what I was trying without creating a table each
time... I was just not constructing my queries correctly so that I could get
all of the data that I needed in the right place at the right time.
I was trying to validate teh data in the temporary table (the one that I am
NOT recreating every week now!) against the last data entered in the
permanant table.
I now just use an update table to bring in the last values entered for each
equipment tag in the temp table and then an append table to wrine the new
weeks values back into the permanant table...
I don't know why it takes me so long to get these things working sometimes,
but at least I can say that when I finally do figure things out they seem to
stick with me...

Thanks again for your help. Your advice is spot on!

Jason M Canady