Insert Into SQL Statement losing data...

[WeeZaliban]

I have been trying to build a SELECT query to drop data from multiple tables into one in order to easily build a report off of that table. I am only focusing on one table at a time and have run into an issue with an INSERT INTO statement.

For one of the fields in the destination table which is formatted as a Long Integer, it loses the numbers after the decimal point. However, the number is accurate when I look at the Datasheet View of the Query before running it as a whole so the issue doesn't appear to be in the SELECT statement.

Example:

Table1.OldField5

83.05

Table2.NewField5

83

Even after tampering with the Format of that Field (making it Fixed, changing Decimal Places to '2'), it is still 83.00.

Below is what I have now and just to be clear, all other data comes through fine. It is only that field that does not accurately carry over the correct values after the decimal point.

INSERT INTO Table2 ( [NewField1], [NewField2], [NewField3], [NewField4], [NewField5], [NewField6], [NewField7], [NewField8] )
SELECT OldField1 As NewField1, OldField2 As NewField2 etc...
FROM Table1
WHERE OldField1 = 'Criteria1' AND OldField10 = 'Criteria2'
GROUP BY OldField1, OldField2, OldField3, OldField4

Any help is greatly appreciated.

 

[Norie]

Integer's don't have decimal places.

The datasheet will show you the data you are updating the table with, not the result.

Why are you using an UPDATE query anyway?

Wouldn't a SELECT query do?

 

[Joe4]

For one of the fields in the destination table which is formatted as a Long Integer, it loses the numbers after the decimal point.

That's because Integers are whole numbers! You cannot store decimals in an Integer field. You will need to use Decimal, Single, or Double.

I have been trying to build a SELECT query to drop data from multiple tables into one in order to easily build a report off of that table.

This is usually not recommended and is probably not even necessary. You can combine data from multiple tables in a query, and use the query as the source of your report. Writing data from tables to other tables can undermine data integrity by threatening the dynamic nature of a database, while usually violating the rules of normalization. Because you can use Queries for just about anything you can use Tables for (Reports, Forms, Exports), there is usually no need to write the data back to a new table.

 

[WeeZaliban]

A SELECT would be more than enough if I didn't have four other tables that I am going to have to join on two different fields.

Three of the five tables share a field with each other while the other two tables may or may not share the second field.

I am trying to tackle this one part at a time instead of getting hung up on making a huge statement from the get go.

 

[Norie]

How about doing it with more than one SELECT query that feed into a single query that is the record source for the report?

 

[WeeZaliban]

That's because Integers are whole numbers!

Yup. That one just dawned on me. Now, I feel more than a little silly.

As for the second part, data normalization has been a huge issue with the raw reports I deal with already since no report actually shares the exact same nomenclature for like data. So, to work around this, I've added fields that 'normalize' the data to be able to instill some sort of integrity to the database and not have floating tables with no relationship to one another; however, my efforts are more than a little imperfect and since the data provided from at least three of the five reports is incomplete, I have few other options than to mash this stuff together into a separate table. Or at least that is how it feels to me.