Append query help

[cricket7]

Hi,
I have 2 tables, let's say A and B. Both tables are basically the
same with a few differences. Table A has records from 1/1/04 to
8/13/06, the date/time field is one field with date/time format, about
10 other fields are in text format but need to be changed to number
format. All other fields are exactly the same and don't need to be
changed. Table B has records from 8/13/06 - 12/31/06, with a date
field with text type and a time field with text type, with the 10
other fields in the proper number type.

I need to make one table to include all records from 1/1/04 to
12/31/06, with date/time field in one field with date/time type, and
the other 10 fields I mentioned to be in the number type and the rest
of the fields to remain the same.

I have tried to do this with a MakeTable querie but that didn't work.
I also tried with an append querie but that didn't work either. I
don't know what I am doing wrong. Each way I tried I ended up only
with the records for 8/13/06 - 12/31/06. I also couldn't get the date/
time field to change from 2 fields to one field with the type as date/
time.

Thanks for any help you can give me.

 

[Ken Snell \(MVP\)]

Post the SQL statement of the "make table" query that you tried to use;
perhaps we can tweak it to give you the desired results.

 

[cricket7]

Post the SQL statement of the "make table" query that you tried to use;
perhaps we can tweak it to give you the desired results.

--

Ken Snell
<MS ACCESS MVP>









- Show quoted text -

 

[cricket7]

I don't know what happened to my most recent post where I put the SQL
statement in but I will post it again.

SELECT tblTrafficStopDocument.TrafficStopID,
tblTrafficStopDocument.DateAndTimeOfStop,
tblTrafficStopDocument083106to123106.OfficerName,
tblTrafficStopDocument083106to123106.OfficerBadgeNumber,
tblTrafficStopDocument083106to123106.NameOfDriver,
tblTrafficStopDocument083106to123106.Address,
tblTrafficStopDocument083106to123106.City,
tblTrafficStopDocument083106to123106.State,
tblTrafficStopDocument083106to123106.ZipCode,
tblTrafficStopDocument083106to123106.VehicleMake,
tblTrafficStopDocument083106to123106.VehicleYear,
tblTrafficStopDocument083106to123106.DriversYearOfBirth,
tblTrafficStopDocument083106to123106.DriverSex,
tblTrafficStopDocument083106to123106.DriverRace,
tblTrafficStopDocument083106to123106.ReasonForStop,
tblTrafficStopDocument083106to123106.TypeOfMovingViolation,
tblTrafficStopDocument083106to123106.ResultOfStop,
tblTrafficStopDocument083106to123106.TypeOfRoadway,
tblTrafficStopDocument083106to123106.BeatLocationOfStop,
tblTrafficStopDocument083106to123106.WasASearchConducted,
tblTrafficStopDocument083106to123106.VehicleSearchType,
tblTrafficStopDocument083106to123106.DriverSearchType,
tblTrafficStopDocument083106to123106.Passenger1SearchType,
tblTrafficStopDocument083106to123106.Passenger2SearchType,
tblTrafficStopDocument083106to123106.Passenger3SearchType,
tblTrafficStopDocument083106to123106.Passenger4SearchType,
tblTrafficStopDocument083106to123106.Passenger5SearchType,
tblTrafficStopDocument083106to123106.Passenger6SearchType,
tblTrafficStopDocument083106to123106.Exported,
tblTrafficStopDocument083106to123106.ContrabandFound,
tblTrafficStopDocument083106to123106.DrugsFound,
tblTrafficStopDocument083106to123106.WeaponFound,
tblTrafficStopDocument083106to123106.StolenPropertyFound,
tblTrafficStopDocument083106to123106.OtherContrabandFound INTO
tblTrafficStopDocumentMaster
FROM tblTrafficStopDocument INNER JOIN
tblTrafficStopDocument083106to123106 ON
tblTrafficStopDocument.TrafficStopID =
tblTrafficStopDocument083106to123106.TrafficStopID;

Thanks,
Pat

 

[Ken Snell \(MVP\)]

Your query does not appear to be selecting any records from the table that
has the '1/1/04 thru 8/13/06' data?

I believe what you want to do is to combine the data from the two tables,
correct? and to put the combined data into a new table.

Best way to do this is to create the table that will receive the combined
data. Create it with the correct fields (with correct data types that you
want -- i.e., use number data type instead of text for those fields whose
data you want to "convert"). From what you've posted, I believe that this
table should have 35 fields -- one new autonumber field and 34 fields from
your current two tables. I'll asssume that this new table is named
"tblNewData". I'll also assume that the names of the 34 fields match the
names that you posted in your SQL statement (e.g., TrafficStopID,
DateAndTimeOfStop, etc.).

(Side note: your query's structure suggests that your data tables are not
normalized -- that you've using separate fields for different aspects of the
same data, e.g., Passenger1SearchType, Passenger2SearchType, etc. While this
unnormalized data structure may not hinder your efficient use of the
database's data right now, most likely it will cause you major problems
later when you want to query for PassengerSearchType independent of whether
it was Passenger1, Passenger2, etc.)

What you want to do, assuming that both of the current tables will be
providing the same number of data fields from each table, is to use a union
query that is then the source of an append query. Let's first build the
union query (name it "qryUnionData") -- in the following SQL statement, I've
assumed that the table with earlier data is named
"tblTrafficStopDocument010104to081306", so change that name if it's not
correct:

SELECT TSD.TrafficStopID,
TSD.DateAndTimeOfStop,
T812.OfficerName,
T812.OfficerBadgeNumber,
T812.NameOfDriver,
T812.Address,
T812.City,
T812.State,
T812.ZipCode,
T812.VehicleMake,
T812.VehicleYear,
T812.DriversYearOfBirth,
T812.DriverSex,
T812.DriverRace,
T812.ReasonForStop,
T812.TypeOfMovingViolation,
T812.ResultOfStop,
T812.TypeOfRoadway,
T812.BeatLocationOfStop,
T812.WasASearchConducted,
T812.VehicleSearchType,
T812.DriverSearchType,
T812.Passenger1SearchType,
T812.Passenger2SearchType,
T812.Passenger3SearchType,
T812.Passenger4SearchType,
T812.Passenger5SearchType,
T812.Passenger6SearchType,
T812.Exported,
T812.ContrabandFound,
T812.DrugsFound,
T812.WeaponFound,
T812.StolenPropertyFound,
T812.OtherContrabandFound INTO
tblTrafficStopDocumentMaster
FROM tblTrafficStopDocument AS TSD INNER JOIN
tblTrafficStopDocument083106to123106 AS T812 ON
TSD.TrafficStopID =
T812.TrafficStopID
UNION ALL
SELECT TSD1.TrafficStopID,
TSD1.DateAndTimeOfStop,
T18.OfficerName,
T18.OfficerBadgeNumber,
T18.NameOfDriver,
T18.Address,
T18.City,
T18.State,
T18.ZipCode,
T18.VehicleMake,
T18.VehicleYear,
T18.DriversYearOfBirth,
T18.DriverSex,
T18.DriverRace,
T18.ReasonForStop,
T18.TypeOfMovingViolation,
T18.ResultOfStop,
T18.TypeOfRoadway,
T18.BeatLocationOfStop,
T18.WasASearchConducted,
T18.VehicleSearchType,
T18.DriverSearchType,
T18.Passenger1SearchType,
T18.Passenger2SearchType,
T18.Passenger3SearchType,
T18.Passenger4SearchType,
T18.Passenger5SearchType,
T18.Passenger6SearchType,
T18.Exported,
T18.ContrabandFound,
T18.DrugsFound,
T18.WeaponFound,
T18.StolenPropertyFound,
T18.OtherContrabandFound INTO
tblTrafficStopDocumentMaster
FROM tblTrafficStopDocument AS TSD1 INNER JOIN
tblTrafficStopDocument010104to081306 AS T18 ON
TSD1.TrafficStopID =
T18 .TrafficStopID;


Then create an append query (name it "qryAppend") that will actually put the
combined data into the new table:

INSERT INTO tblNewData
( TrafficStopID,
DateAndTimeOfStop,
OfficerName,
OfficerBadgeNumber,
NameOfDriver,
Address,
City,
State,
ZipCode,
VehicleMake,
VehicleYear,
DriversYearOfBirth,
DriverSex,
DriverRace,
ReasonForStop,
TypeOfMovingViolation,
ResultOfStop,
TypeOfRoadway,
BeatLocationOfStop,
WasASearchConducted,
VehicleSearchType,
DriverSearchType,
Passenger1SearchType,
Passenger2SearchType,
Passenger3SearchType,
Passenger4SearchType,
Passenger5SearchType,
Passenger6SearchType,
Exported,
ContrabandFound,
DrugsFound,
WeaponFound,
StolenPropertyFound,
OtherContrabandFound )
SELECT * FROM qryUnionData;


That should do what you seek.
--

Ken Snell
<MS ACCESS MVP>

 

[cricket7]

Your query does not appear to be selecting any records from the table that
has the '1/1/04 thru 8/13/06' data?

I believe what you want to do is to combine the data from the two tables,
correct? and to put the combined data into a new table.

Best way to do this is to create the table that will receive the combined
data. Create it with the correct fields (with correct data types that you
want -- i.e., use number data type instead of text for those fields whose
data you want to "convert"). From what you've posted, I believe that this
table should have 35 fields -- one new autonumber field and 34 fields from
your current two tables. I'll asssume that this new table is named
"tblNewData". I'll also assume that the names of the 34 fields match the
names that you posted in your SQL statement (e.g., TrafficStopID,
DateAndTimeOfStop, etc.).

(Side note: your query's structure suggests that your data tables are not
normalized -- that you've using separate fields for different aspects of the
same data, e.g., Passenger1SearchType, Passenger2SearchType, etc. While this
unnormalized data structure may not hinder your efficient use of the
database's data right now, most likely it will cause you major problems
later when you want to query for PassengerSearchType independent of whether
it was Passenger1, Passenger2, etc.)

What you want to do, assuming that both of the current tables will be
providing the same number of data fields from each table, is to use a union
query that is then the source of an append query. Let's first build the
union query (name it "qryUnionData") -- in the following SQL statement, I've
assumed that the table with earlier data is named
"tblTrafficStopDocument010104to081306", so change that name if it's not
correct:

SELECT TSD.TrafficStopID,
TSD.DateAndTimeOfStop,
T812.OfficerName,
T812.OfficerBadgeNumber,
T812.NameOfDriver,
T812.Address,
T812.City,
T812.State,
T812.ZipCode,
T812.VehicleMake,
T812.VehicleYear,
T812.DriversYearOfBirth,
T812.DriverSex,
T812.DriverRace,
T812.ReasonForStop,
T812.TypeOfMovingViolation,
T812.ResultOfStop,
T812.TypeOfRoadway,
T812.BeatLocationOfStop,
T812.WasASearchConducted,
T812.VehicleSearchType,
T812.DriverSearchType,
T812.Passenger1SearchType,
T812.Passenger2SearchType,
T812.Passenger3SearchType,
T812.Passenger4SearchType,
T812.Passenger5SearchType,
T812.Passenger6SearchType,
T812.Exported,
T812.ContrabandFound,
T812.DrugsFound,
T812.WeaponFound,
T812.StolenPropertyFound,
T812.OtherContrabandFound INTO
tblTrafficStopDocumentMaster
FROM tblTrafficStopDocument AS TSD INNER JOIN
tblTrafficStopDocument083106to123106 AS T812 ON
TSD.TrafficStopID =
T812.TrafficStopID
UNION ALL
SELECT TSD1.TrafficStopID,
TSD1.DateAndTimeOfStop,
T18.OfficerName,
T18.OfficerBadgeNumber,
T18.NameOfDriver,
T18.Address,
T18.City,
T18.State,
T18.ZipCode,
T18.VehicleMake,
T18.VehicleYear,
T18.DriversYearOfBirth,
T18.DriverSex,
T18.DriverRace,
T18.ReasonForStop,
T18.TypeOfMovingViolation,
T18.ResultOfStop,
T18.TypeOfRoadway,
T18.BeatLocationOfStop,
T18.WasASearchConducted,
T18.VehicleSearchType,
T18.DriverSearchType,
T18.Passenger1SearchType,
T18.Passenger2SearchType,
T18.Passenger3SearchType,
T18.Passenger4SearchType,
T18.Passenger5SearchType,
T18.Passenger6SearchType,
T18.Exported,
T18.ContrabandFound,
T18.DrugsFound,
T18.WeaponFound,
T18.StolenPropertyFound,
T18.OtherContrabandFound INTO
tblTrafficStopDocumentMaster
FROM tblTrafficStopDocument AS TSD1 INNER JOIN
tblTrafficStopDocument010104to081306 AS T18 ON
TSD1.TrafficStopID =
T18 .TrafficStopID;

Then create an append query (name it "qryAppend") that will actually put the
combined data into the new table:

INSERT INTO tblNewData
( TrafficStopID,
DateAndTimeOfStop,
OfficerName,
OfficerBadgeNumber,
NameOfDriver,
Address,
City,
State,
ZipCode,
VehicleMake,
VehicleYear,
DriversYearOfBirth,
DriverSex,
DriverRace,
ReasonForStop,
TypeOfMovingViolation,
ResultOfStop,
TypeOfRoadway,
BeatLocationOfStop,
WasASearchConducted,
VehicleSearchType,
DriverSearchType,
Passenger1SearchType,
Passenger2SearchType,
Passenger3SearchType,
Passenger4SearchType,
Passenger5SearchType,
Passenger6SearchType,
Exported,
ContrabandFound,
DrugsFound,
WeaponFound,
StolenPropertyFound,
OtherContrabandFound )
SELECT * FROM qryUnionData;

That should do what you seek.
--

Ken Snell
<MS ACCESS MVP>









- Show quoted text -

Thanks so very much.

I will try and put your code in where the other code was. I assume
that is what I am supposed to do? I don't know anything about code.
I don't suppose I can just do a MakeTable query and include certain
fields from each table, (Actually I think that is what I tried to do
in the beginning and messed up.) I guess I am confused about where I
put the code you gave me. Do I look for where I found the code I put
in my email to you and then just do a copy and paste into the same
place copying over the old code?

cricket7

 

[Ken Snell \(MVP\)]

Thanks so very much.

I will try and put your code in where the other code was. I assume
that is what I am supposed to do? I don't know anything about code.
I don't suppose I can just do a MakeTable query and include certain
fields from each table, (Actually I think that is what I tried to do
in the beginning and messed up.) I guess I am confused about where I
put the code you gave me. Do I look for where I found the code I put
in my email to you and then just do a copy and paste into the same
place copying over the old code?

cricket7

The information that I posted is an SQL statement for a query (it's not
"code"). It can be pasted into the SQL view of a query. I am guessing that
that is the view you used to get the original SQL statement that you posted,
so yes, you can paste my SQL statement into the SQL view in place of the one
you had. Note that I have not debugged or tested the statement that I posted
because I don't have a copy of your tables and data, so there may be some
errors in it. < s >

 

[cricket7]

The information that I posted is an SQL statement for a query (it's not
"code"). It can be pasted into the SQL view of a query. I am guessing that
that is the view you used to get the original SQL statement that you posted,
so yes, you can paste my SQL statement into the SQL view in place of the one
you had. Note that I have not debugged or tested the statement that I posted
because I don't have a copy of your tables and data, so there may be some
errors in it. < s >

Ken,

I made a new table as you suggested with all the fields/types I wanted
with the combination of the two tables previously stated.
tblTrafficStopDocument010104to081306 and
tblTrafficstopDocument081306to123106
The new table is called tblNewData.

I then attempted to do a union query but when I pasted your sql
statement in and tried to run it I received the error message "An
actin query cannot be used as a row source". What did I do wrong?

As you stated earlier "I believe what you want to do is to combine the
data from the two tables, correct? and to put the combined data into
a new table." Exactly. But the date field in the
tblTrafficstopDocument081306to123106 table is date/time type and the
time field in the same table is in text type. These 2 fields are
combined in the tblTrafficStopDocument010104to081306 table and that is
how I would like them to all be in the tblNewData. So with me having
a new table with all the fields and types like I want, will the append
querie put the records into the new table with different field
types?

What the problem is is that this database was written in the first
place by Illinois Dept Of Traffic and they had only text fields for
the types. This made it impossible to do all the queries I needed for
the racial profiling data for the chief.

I hope this makes sense. Thanks so much for you help. I'm sorry it
is taking me so long to get it right.
Pat (cricket7)

 

[Ken Snell \(MVP\)]

Where are you trying to paste the SQL statement? You cannot use the SQL as
the Row Source of a combo box or of a list box. You'd need to create a
query, don't select any tables, close the table selection window, select SQL
View from toolbar, and paste the SQL statement there. Save the query with a
name. Use that name in your Row Source property.

A union query requires you to have same data type for all fields that are
being "stacked". So, if one field has a date/time data type and the other
has a text form, something like this would be done -- using the CDate
function to convert a text time to a date/time time:

SELECT Table1.DateTimeFieldName
FROM Table1
UNION ALL
SELECT CDate(Table2.TextTimeFieldName) AS T
FROM Table2;

--

Ken Snell
<MS ACCESS MVP>