I have spent several hours looking for this answer in the Discussion group as well as Access help. Am sure its simple I just must be dense.
1. My query is leaving out items that do not have an "InsCode" in the Claims table. Not sure how to fix it. The field is not required in the Claims table. It works in another report that only pulls that field where this one is predicated on Claims Approval date being NULL. If I go into the Claims table and manually enter it then it appears on the Report. Here is the Code.
SELECT DISTINCTROW RODetails.Est, Arrivals.ArrivalDate, Customers.CustLName, [Color Code to Cladding Lookup Table].ExtBodyColor, Customers.CustFName, Claims.DateSupReq, [DateSupReq]-[ArrivalDate] AS [#2-#1], Claims.DateSupWrote, [DateSupWrote]-[DateSupReq] AS [#3-#2], Date() AS Exp1, Date()-[DateSupWrote] AS WOSA, Date()-[ArrivalDate] AS CT, Claims.DateSupApvd, Jobs.[GT#], Arrivals.[RO#], RODetails.[RO#], Claims.[RO#], RODetails.EBWH, RODetails.ETOH, Vehicles.[Vin#], Vehicles.Year, Vehicles.Model, Vehicles.License, Vehicles.CustLName, Jobs.[RO#], Jobs.[VIN#], Jobs.Delivered, [Insurance Companies].ICName,
Claims.InsCode,
[Insurance Status Code Lookup Table].ICDescShort
FROM [Insurance Status Code Lookup Table] INNER JOIN ([Insurance Companies] INNER JOIN ([Color Code to Cladding Lookup Table] INNER JOIN ((((Customers INNER JOIN Vehicles ON (Customers.CustFName = Vehicles.CustFName) AND (Customers.CustLName = Vehicles.CustLName)) INNER JOIN (Arrivals INNER JOIN Jobs ON Arrivals.[RO#] = Jobs.[RO#]) ON Vehicles.[Vin#] = Jobs.[VIN#]) INNER JOIN RODetails ON Jobs.[RO#] = RODetails.[RO#]) INNER JOIN Claims ON (Arrivals.[RO#] = Claims.[RO#]) AND (Jobs.[RO#] = Claims.[RO#])) ON ([Color Code to Cladding Lookup Table].PCode = Vehicles.PCode) AND ([Color Code to Cladding Lookup Table].PCode = Vehicles.PCode)) ON [Insurance Companies].ICCode = Claims.ICCode) ON [Insurance Status Code Lookup Table].InsCode = Claims.InsCode
WHERE (((Claims.DateSupApvd) Is Null) AND ((Jobs.Delivered) Is Null))
ORDER BY RODetails.Est, Arrivals.ArrivalDate;
2. As a followup to 1 - I really want a default "InsCode" but the table doesn't have a spot for default so I told the form to default but they can create a record without going to that form and if I do go to the form I don't see my default because the table is already created with a null field any suggestions?
3. Inherited this database - noticed in some of the Queries it has the RO# field from several different tables. RO# is the key on these tables. Shouldn't I only need to have one of them listed as a field for my query?
4. Some of the reports seem like they could be streamlined. There is a separate query and report for everyone of my techs. Since their jobs could be in 5 different stages of completion it states:
if stage1 = tech1
if stage2 = tech1
if stage3 = tech1 etc and then sorts on stage for printing with a separate menu item for each tech.
Couldn't I highlight one or more techs from a pull down list and have it insert them into the query? Everytime I make a small change I have to change 15 queries and reports.
Am trying to simplify as I learn.
1. My query is leaving out items that do not have an "InsCode" in the Claims table. Not sure how to fix it. The field is not required in the Claims table. It works in another report that only pulls that field where this one is predicated on Claims Approval date being NULL. If I go into the Claims table and manually enter it then it appears on the Report. Here is the Code.
SELECT DISTINCTROW RODetails.Est, Arrivals.ArrivalDate, Customers.CustLName, [Color Code to Cladding Lookup Table].ExtBodyColor, Customers.CustFName, Claims.DateSupReq, [DateSupReq]-[ArrivalDate] AS [#2-#1], Claims.DateSupWrote, [DateSupWrote]-[DateSupReq] AS [#3-#2], Date() AS Exp1, Date()-[DateSupWrote] AS WOSA, Date()-[ArrivalDate] AS CT, Claims.DateSupApvd, Jobs.[GT#], Arrivals.[RO#], RODetails.[RO#], Claims.[RO#], RODetails.EBWH, RODetails.ETOH, Vehicles.[Vin#], Vehicles.Year, Vehicles.Model, Vehicles.License, Vehicles.CustLName, Jobs.[RO#], Jobs.[VIN#], Jobs.Delivered, [Insurance Companies].ICName,
Claims.InsCode,
[Insurance Status Code Lookup Table].ICDescShort
FROM [Insurance Status Code Lookup Table] INNER JOIN ([Insurance Companies] INNER JOIN ([Color Code to Cladding Lookup Table] INNER JOIN ((((Customers INNER JOIN Vehicles ON (Customers.CustFName = Vehicles.CustFName) AND (Customers.CustLName = Vehicles.CustLName)) INNER JOIN (Arrivals INNER JOIN Jobs ON Arrivals.[RO#] = Jobs.[RO#]) ON Vehicles.[Vin#] = Jobs.[VIN#]) INNER JOIN RODetails ON Jobs.[RO#] = RODetails.[RO#]) INNER JOIN Claims ON (Arrivals.[RO#] = Claims.[RO#]) AND (Jobs.[RO#] = Claims.[RO#])) ON ([Color Code to Cladding Lookup Table].PCode = Vehicles.PCode) AND ([Color Code to Cladding Lookup Table].PCode = Vehicles.PCode)) ON [Insurance Companies].ICCode = Claims.ICCode) ON [Insurance Status Code Lookup Table].InsCode = Claims.InsCode
WHERE (((Claims.DateSupApvd) Is Null) AND ((Jobs.Delivered) Is Null))
ORDER BY RODetails.Est, Arrivals.ArrivalDate;
2. As a followup to 1 - I really want a default "InsCode" but the table doesn't have a spot for default so I told the form to default but they can create a record without going to that form and if I do go to the form I don't see my default because the table is already created with a null field any suggestions?
3. Inherited this database - noticed in some of the Queries it has the RO# field from several different tables. RO# is the key on these tables. Shouldn't I only need to have one of them listed as a field for my query?
4. Some of the reports seem like they could be streamlined. There is a separate query and report for everyone of my techs. Since their jobs could be in 5 different stages of completion it states:
if stage1 = tech1
if stage2 = tech1
if stage3 = tech1 etc and then sorts on stage for printing with a separate menu item for each tech.
Couldn't I highlight one or more techs from a pull down list and have it insert them into the query? Everytime I make a small change I have to change 15 queries and reports.
Am trying to simplify as I learn.
