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

Access crash caused by large sql

P: n/a
I have a report SQL which have been working for a few weeks. Its large
and the target database is large. It has 59 join, and I dont think
there is anything I can do about this, since the DB I use has been
normalized by pros.

My problem is I try to had another join to display another value, then
I get a crash report from MS-Access. Anything I can to do diagnose
this problem. My development depends on this SQL:

SELECT
BASE_VEHICLE_ID,
VEHICLE_ID,
Trim(Make.MakeName) as Make,
Trim(Model.ModelName) as Model,
YearID,
Trim(SubModel.SubModelName) as Sub,
Trim(DriveType.DriveTypeName) as DriveType,
BodyNumDoors.BodyNumDoors + "DR" as Doors,
Trim(BodyType.BodyTypeName) as Body,
Trim(TransmissionType.TransmissionTypeName) as Trans,
Trim(TransmissionNumSpeeds.TransmissionNumSpeeds) as TransSpeeds,
Trim(TransmissionControlType.TransmissionControlTy peName) as
TransControl,
EngineBase.Liter + "L" as EngLiter,
EngineBase.CC as EngCC,
EngineBase.Cylinders + " CYL" as EngCyl,
Trim(EngineDesignation.EngineDesignationName) as EngineName,
Trim(Aspiration.AspirationName) as Aspiration,
Trim(FuelType.FuelTypeName) as Fuel,
BrakeType1.BrakeTypeName as FrontBrake,
BrakeType2.BrakeTypeName as RearBrake,
BrakeSystem.BrakeSystemName as BrakeName,
BrakeABS.BrakeABSName as ABS,
PART_NUMBER,
FITMENT_NOTE_DESC
INTO
RAPPORT_MAPPING
FROM
APPLICATION_ACES,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToTransmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionNumSpeeds,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionControlType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToEngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignation,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToBrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType1,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType2,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
PART,
FITMENT_NOTES_LEGACY
WHERE
BaseVehicle.BaseVehicleID = APPLICATION_ACES.BASE_VEHICLE_ID
AND
Make.MakeID = BaseVehicle.MakeId
AND
Model.ModelID = BaseVehicle.ModelId
AND
Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
Vehicle.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
SubModel.SubModelId = Vehicle.SubModelId
AND
DriveType.DriveTypeID = APPLICATION_ACES.DRIVE_TYPE_ID
AND
VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
BodyType.BodyTypeID = BodyStyleConfig.BodyTypeID
AND
VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
Transmission.TransmissionID = VehicleToTransmission.TransmissionID
AND
TransmissionBase.TransmissionBaseID =
Transmission.TransmissionBaseID
AND
TransmissionType.TransmissionTypeID =
TransmissionBase.TransmissionTypeID
AND
TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
VehicleToEngineConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
EngineConfig.EngineConfigID = VehicleToEngineConfig.EngineConfigID
AND
EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
EngineDesignation.EngineDesignationID =
EngineConfig.EngineDesignationID
AND
Aspiration.AspirationID = EngineConfig.AspirationID
AND
FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
VehicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
PART.PART_ID = APPLICATION_ACES.PART_ID
AND
FITMENT_NOTES_LEGACY.FITMENT_NOTE_ID =
APPLICATION_ACES.FITMENT_NOTE_ID
;

Aug 24 '08 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Another question: is there a way to turn of the questions "you are
about to insert xx records", and the other one "the table will be
suppressed...". Because of the time the query needs to complete, I'm
in bed...Thanks.
Aug 24 '08 #2

P: n/a
Harel <gu********@gmail.comwrote in news:b1752fc5-3616-4479-b62e-
4c**********@d45g2000hsc.googlegroups.com:
Another question: is there a way to turn of the questions "you are
about to insert xx records", and the other one "the table will be
suppressed...". Because of the time the query needs to complete, I'm
in bed...Thanks.
in your previous message you said that the SQL was for a report.
Reports in Access do not use MakeTable queries, so you should not get
the above messages.

If you are actualy making a new table to use in the report, using
DoCmd.openQuery, prefix the statement with DoCmd.setwarnings false, and
follow it with Docmd.setwarnings true.

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
Aug 24 '08 #3

P: n/a
You need to split the SQL into several smaller queries, the first
being an insert query, then update the related fields using update
queries.

Also, your code is using where clauses to enforce the relationshps.
That way is obsolete, use Joins instead, as they are more efficient.

Q

Harel <gu********@gmail.comwrote in
news:85c81575-13b2-4a99-9a0f-
7f**********@j22g2000hsf.googlegroups.co
m:
I have a report SQL which have been working for a few weeks. Its
large and the target database is large. It has 59 join, and I dont
think there is anything I can do about this, since the DB I use
has been normalized by pros.

My problem is I try to had another join to display another value,
then I get a crash report from MS-Access. Anything I can to do
diagnose this problem. My development depends on this SQL:

SELECT
BASE_VEHICLE_ID,
VEHICLE_ID,
Trim(Make.MakeName) as Make,
Trim(Model.ModelName) as Model,
YearID,
Trim(SubModel.SubModelName) as Sub,
Trim(DriveType.DriveTypeName) as DriveType,
BodyNumDoors.BodyNumDoors + "DR" as Doors,
Trim(BodyType.BodyTypeName) as Body,
Trim(TransmissionType.TransmissionTypeName) as Trans,
Trim(TransmissionNumSpeeds.TransmissionNumSpeeds) as
TransSpeeds,
Trim(TransmissionControlType.TransmissionControlTy peName) as
TransControl,
EngineBase.Liter + "L" as EngLiter,
EngineBase.CC as EngCC,
EngineBase.Cylinders + " CYL" as EngCyl,
Trim(EngineDesignation.EngineDesignationName) as EngineName,
Trim(Aspiration.AspirationName) as Aspiration,
Trim(FuelType.FuelTypeName) as Fuel,
BrakeType1.BrakeTypeName as FrontBrake,
BrakeType2.BrakeTypeName as RearBrake,
BrakeSystem.BrakeSystemName as BrakeName,
BrakeABS.BrakeABSName as ABS,
PART_NUMBER,
FITMENT_NOTE_DESC
INTO
RAPPORT_MAPPING
FROM
APPLICATION_ACES,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToTransmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionNumSpeeds,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionControlType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToEngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignation,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType1,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType2,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
[MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco
AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
PART,
FITMENT_NOTES_LEGACY
WHERE
BaseVehicle.BaseVehicleID = APPLICATION_ACES.BASE_VEHICLE_ID
AND
Make.MakeID = BaseVehicle.MakeId
AND
Model.ModelID = BaseVehicle.ModelId
AND
Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
Vehicle.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
SubModel.SubModelId = Vehicle.SubModelId
AND
DriveType.DriveTypeID = APPLICATION_ACES.DRIVE_TYPE_ID
AND
VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
BodyType.BodyTypeID = BodyStyleConfig.BodyTypeID
AND
VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
Transmission.TransmissionID =
VehicleToTransmission.TransmissionID
AND
TransmissionBase.TransmissionBaseID =
Transmission.TransmissionBaseID
AND
TransmissionType.TransmissionTypeID =
TransmissionBase.TransmissionTypeID
AND
TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
VehicleToEngineConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
EngineConfig.EngineConfigID =
VehicleToEngineConfig.EngineConfigID
AND
EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
EngineDesignation.EngineDesignationID =
EngineConfig.EngineDesignationID
AND
Aspiration.AspirationID = EngineConfig.AspirationID
AND
FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
VehicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
PART.PART_ID = APPLICATION_ACES.PART_ID
AND
FITMENT_NOTES_LEGACY.FITMENT_NOTE_ID =
APPLICATION_ACES.FITMENT_NOTE_ID
;


--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
Aug 24 '08 #4

P: n/a
Have you thought about a career as a comedy writer?

On Aug 24, 9:24*am, Harel <guyhare...@gmail.comwrote:
I have a report SQL which have been working for a few weeks. Its large
and the target database is large. It has 59 join, and I dont think
there is anything I can do about this, since the DB I use has been
normalized by pros.

My problem is I try to had another join to display another value, then
I get a crash report from MS-Access. Anything I can to do diagnose
this problem. My development depends on this SQL:

SELECT
* BASE_VEHICLE_ID,
* VEHICLE_ID,
* Trim(Make.MakeName) as Make,
* Trim(Model.ModelName) as Model,
* YearID,
* Trim(SubModel.SubModelName) as Sub,
* Trim(DriveType.DriveTypeName) as DriveType,
* BodyNumDoors.BodyNumDoors + "DR" as Doors,
* Trim(BodyType.BodyTypeName) as Body,
* Trim(TransmissionType.TransmissionTypeName) as Trans,
* Trim(TransmissionNumSpeeds.TransmissionNumSpeeds) as TransSpeeds,
* Trim(TransmissionControlType.TransmissionControlTy peName) as
TransControl,
* EngineBase.Liter + "L" as EngLiter,
* EngineBase.CC as EngCC,
* EngineBase.Cylinders + " CYL" as EngCyl,
* Trim(EngineDesignation.EngineDesignationName) as EngineName,
* Trim(Aspiration.AspirationName) as Aspiration,
* Trim(FuelType.FuelTypeName) as Fuel,
* BrakeType1.BrakeTypeName as FrontBrake,
* BrakeType2.BrakeTypeName as RearBrake,
* BrakeSystem.BrakeSystemName as BrakeName,
* BrakeABS.BrakeABSName as ABS,
* PART_NUMBER,
* FITMENT_NOTE_DESC
INTO
* RAPPORT_MAPPING
FROM
* APPLICATION_ACES,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BaseVehicle,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Vehicle,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Make,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Model,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].SubModel,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].DriveType,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].VehicleToBodyStyleConfig,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyStyleConfig,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyNumDoors,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BodyType,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToTransmission,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Transmission,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionBase,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionType,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].TransmissionNumSpeeds,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97
20080731.mdb].TransmissionControlType,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToEngineConfig,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineConfig,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineBase,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].EngineDesignation,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].Aspiration,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].FuelType,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].VehicleToBrakeConfig,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeConfig,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType1,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeType as
BrakeType2,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeSystem,
* [MS Access;DATABASE=D:\Dossiers courants\Projets\Absco\Absco AAIA DB
\ACES\Vcdb\AAIA VCdb2006 Access97 20080731.mdb].BrakeABS,
* PART,
* FITMENT_NOTES_LEGACY
WHERE
* BaseVehicle.BaseVehicleID = APPLICATION_ACES.BASE_VEHICLE_ID
AND
* Make.MakeID = BaseVehicle.MakeId
AND
* Model.ModelID = BaseVehicle.ModelId
AND
* Vehicle.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
* Vehicle.BaseVehicleID = BaseVehicle.BaseVehicleID
AND
* Vehicle.SubModelId = APPLICATION_ACES.SUBMODEL_ID
AND
* SubModel.SubModelId = Vehicle.SubModelId
AND
* DriveType.DriveTypeID = APPLICATION_ACES.DRIVE_TYPE_ID
AND
* VehicleToBodyStyleConfig.BodyStyleConfigID =
APPLICATION_ACES.BODY_STYLE_CONFIG_ID
AND
* VehicleToBodyStyleConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
* BodyStyleConfig.BodyStyleConfigID =
VehicleToBodyStyleConfig.BodyStyleConfigID
AND
* BodyNumDoors.BodyNumDoorsID = BodyStyleConfig.BodyNumDoorsID
AND
* BodyType.BodyTypeID = BodyStyleConfig.BodyTypeID
AND
* VehicleToTransmission.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
* VehicleToTransmission.TransmissionID =
APPLICATION_ACES.TRANSMISSION_ID
AND
* Transmission.TransmissionID = VehicleToTransmission.TransmissionID
AND
* TransmissionBase.TransmissionBaseID =
Transmission.TransmissionBaseID
AND
* TransmissionType.TransmissionTypeID =
TransmissionBase.TransmissionTypeID
AND
* TransmissionNumSpeeds.TransmissionNumSpeedsID =
TransmissionBase.TransmissionNumSpeedsID
AND
* TransmissionControlType.TransmissionControlTypeID =
TransmissionBase.TransmissionControlTypeID
AND
* VehicleToEngineConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
* VehicleToEngineConfig.EngineConfigID =
APPLICATION_ACES.ENGINE_CONFIG_ID
AND
* EngineConfig.EngineConfigID = VehicleToEngineConfig.EngineConfigID
AND
* EngineBase.EngineBaseID = EngineConfig.EngineBaseID
AND
* EngineDesignation.EngineDesignationID =
EngineConfig.EngineDesignationID
AND
* Aspiration.AspirationID = EngineConfig.AspirationID
AND
* FuelType.FuelTypeID = EngineConfig.FuelTypeID
AND
* VehicleToBrakeConfig.VehicleID = APPLICATION_ACES.VEHICLE_ID
AND
* VehicleToBrakeConfig.BrakeConfigID =
APPLICATION_ACES.BRAKE_CONFIG_ID
AND
* BrakeConfig.BrakeConfigID = VehicleToBrakeConfig.BrakeConfigID
AND
* BrakeType1.BrakeTypeID = BrakeConfig.FrontBrakeTypeID
AND
* BrakeType2.BrakeTypeID = BrakeConfig.RearBrakeTypeID
AND
* BrakeSystem.BrakeSystemID = BrakeConfig.BrakeSystemID
AND
* BrakeABS.BrakeABSID = BrakeConfig.BrakeABSID
AND
* PART.PART_ID = APPLICATION_ACES.PART_ID
AND
* FITMENT_NOTES_LEGACY.FITMENT_NOTE_ID =
APPLICATION_ACES.FITMENT_NOTE_ID
;
Aug 24 '08 #5

P: n/a
On 24 août, 12:58, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Have you thought about a career as a comedy writer?
What is it you dont like about the post?
Aug 24 '08 #6

P: n/a
On Aug 24, 2:15*pm, Harel <guyhare...@gmail.comwrote:
On 24 août, 12:58, lyle fairfield <lyle.fairfi...@gmail.comwrote:
Have you thought about a career as a comedy writer?

What is it you dont like about the post?
I love the post. I also love Lucy, Seinfeld, Jackie, Red and Carole.
Aug 24 '08 #7

P: n/a
I love the post. I also love Lucy, Seinfeld, Jackie, Red and Carole.

Ok then. I prefer "tragedy" anyway...

Aug 24 '08 #8

P: n/a
Harel <gu********@gmail.comwrote in news:141cc46b-faa8-404f-8c74-
15**********@k37g2000hsf.googlegroups.com:
>I love the post. I also love Lucy, Seinfeld, Jackie, Red and Carole.

Ok then. I prefer "tragedy" anyway...
The tragedy is in the way you wrote the SQL. :-)

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
Aug 24 '08 #9

P: n/a
Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.175.223.2:
Also, your code is using where clauses to enforce the
relationshps. That way is obsolete, use Joins instead, as they are
more efficient.
That depends on the circumstances. Most WHERE clauses are optimized
by the Jet query optimizer exactly the same way as a join.

But not all of them.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 24 '08 #10

P: n/a
I don't understand why you're specifying the path for all but three
tables, when you really need to do it for the three. Seems to me it
would be much simpler to do this:

FROM [connect string for this db].APPLICATION_ACES, [connect string
for this db].PART, [connect string for this
db].FITMENT_NOTES_LEGACY, BaseVehicle, Vehicle, Make, Model,
SubModel, DriveType, VehicleToBodyStyleConfig, BodyStyleConfig,
BodyNumDoors, BodyType, VehicleToTransmission, Transmission,
TransmissionBase, TransmissionType, TransmissionNumSpeeds,
TransmissionControlType, VehicleToEngineConfig, EngineConfig,
EngineBase, EngineDesignation, Aspiration, FuelType,
VehicleToBrakeConfig, BrakeConfig, BrakeType as BrakeType1,
BrakeType as BrakeType2, BrakeSystem, BrakeABS IN 'D:\Dossiers
courants\Projets\Absco\Absco AAIA DB\ACES\Vcdb\AAIA VCdb2006
Access97 20080731.mdb'

Since all your tables but 3 of them are in the same MDB, it makes
more sense to specify the connect string for those THREE and use IN
to specify the MDB for all the other tables.

From there, you might be able to resolve your issue and use joins in
place of a WHERE clause.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 24 '08 #11

P: n/a
On database window
Click "Queries" then click " Create query in Design view "
And start from the beginning.
Create your first query then save it.
Click again " Create query in Design view "
Create your second - if necessary onto the first one.
Basing or joining other queries your previously created should be
pencilled in your case.
How many queries ? Well, looking at your sql I'd say more than one
surely.
I mean if the word of "diagnose " is creating some difficulty.

kind rgds
Aug 26 '08 #12

This discussion thread is closed

Replies have been disabled for this discussion.