Description of identifier not appearing 1

[ShabanaHafiz]

I asked this question in another thread of mine, thread701-1365250, but could not get reply. May be because it was in continuation of my previous question and I should have avoided asking multiple questions in a single thread. So, I am writing my question again in this new thread.

I am using Microsoft Access 2003.

In an Access Query, I have one table and one query; LogBook, VHCostPerKM (Vehicle Cost Per KM).

Fields in LogBook table are:
ProjectID
OfficeID
VehicleID
DateOfTravel
KMBeforeTravel
KMAfterTravel

Fields in VHCostPerKM query are:
OfficeID
VehicleID
UnitCost

For a given DateOfTravel, I need to calculate for each ProjectID, OfficeID and VehicleID the following:

Sum of KM Traveled
Cost per Project

The SQL View of my query is as follows:

SELECT LogBook.ProjectID, LogBook.OfficeID, LogBook.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled], Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost]) AS [Cost per Project]
FROM VHCostPerKM INNER JOIN LogBook ON (VHCostPerKM.OfficeID = LogBook.OfficeID) AND (VHCostPerKM.VehicleID = LogBook.VehicleID)
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY LogBook.ProjectID, LogBook.OfficeID, LogBook.VehicleID;

I also need to show description in place of ProjectID, OfficeID and VehicleID.

I added Project table and replaced ProjectID with Description field of Project Table. This worked fine and at this stage, SQL View was as follows:

SELECT Project.Description AS Project, LogBook.OfficeID, LogBook.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled], Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost]) AS [Cost per Project]
FROM (VHCostPerKM INNER JOIN LogBook ON (VHCostPerKM.OfficeID = LogBook.OfficeID) AND (VHCostPerKM.VehicleID = LogBook.VehicleID)) INNER JOIN Project ON LogBook.ProjectID = Project.ProjectID
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY Project.Description, LogBook.OfficeID, LogBook.VehicleID;

Then I added Office table and replaced OfficeID with Description field of Office Table. When I executed the query this time, Office column appeared blank. At this stage, SQL View was as follows:

SELECT Project.Description AS Project, Office.Description AS Office, LogBook.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled], Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost]) AS [Cost per Project]
FROM ((VHCostPerKM INNER JOIN LogBook ON (VHCostPerKM.OfficeID = LogBook.OfficeID) AND (VHCostPerKM.VehicleID = LogBook.VehicleID)) INNER JOIN Project ON LogBook.ProjectID = Project.ProjectID) INNER JOIN Office ON LogBook.OfficeID = Office.OfficeID
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY Project.Description, Office.Description, LogBook.VehicleID;

 

[dhookom]

It looks like you have a table named Office and are creating a field named Office. I would first change this to
Office.Description AS OfficeName
If this doesn't fix your problem, try create a query with just the Office and LogBook tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ShabanaHafiz]

Thanks dhookom.

It worked perfectly.

I also did the same for VehicleID and it worked too:

Vehicle.Description AS VehicleName

Can you please explain why did it work for ProjectID? For Project Description, I wrote:

Project.Description AS Project

In this case also, I have a table named Project and am creating a field named Project but it worked??

 

[dhookom]

I don't know why it did or didn't work. I always and consistently apply naming conventions to all of my tables, fields, memory variables, forms, reports, queries,... This prevents issues.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]