help with Query?

[Capt'n]

Hi,
I am using Win/xp and MSAccess2000.

I am not very good with sql syntax. But I mave two table with a
relationship of One to Many.
What I want is just one row from the MANY table (Mainetenace). This
qry return all rows from Maintenance table for an Asset.

How can I code this to get just one row from the maintenance table?

thanks,
Capt'n

here is my syntax:

SELECT [Assets].[StatusID], [Assets].[AssetDescription], [Maintenance
Latest Date].[AssetID], [Maintenance Latest Date].[MaintenanceDate],
DateDiff("ww",[maintenancedate],Now()) AS elaspedweeks,
[Assets].[MainAvgUsageHrs], [Assets].[DaysInWorkWeek],
[Assets].[OperatingHours],
Round(([operatinghours]-DateDiff("ww",[maintenancedate],Now())*[daysinworkweek])/[MainAvgUsageHrs])
AS NextMaintDays,
DateAdd("ww",Round([NextMaintDays]/[daysInWorkWeek]),[MaintenanceDate])
AS NextMaintenanceDate, [Assets].[LocationID], [Locations].[Location]
FROM Locations INNER JOIN ([Maintenance Latest Date] INNER JOIN Assets
ON [Maintenance Latest Date].[AssetID]=[Assets].[AssetID]) ON
[Locations].[LocationID]=[Assets].[LocationID]
WHERE ((([Assets].[StatusID])=1));

 

[Jeff Boyce]

If you have 'many' rows per Asset, which one do you want?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Capt'n]

Jeff,
I need the one that has the latest "maintenancedate". This table is
indexed by AssetID asc, maintenancedate Desc

Thanks,

Capt'n

 

[Jeff Boyce]

So, you don't even need the Asset table if the Maintenance table has AssetID
in it.

You could create a Totals query on the Maintenance table, GroupBy AssetID
and "Max" of MaintenanceDate.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Capt'n]

Jeff,
I think I need the Asset table to get the AssetDescription. The
Maintenance table only carries the AssetID whick is an long Integer.

Capt'n

 

[Jeff Boyce]

One step at a time... can you get the query that I last proposed?

Once that's working, you can use that query as input in a second query that
joins the first query back to the Asset table to pick up the Description.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Capt'n]

Jeff,
I did not see a query that you posted? And I donot know how to code a
total query.

If you could guide me through this it would of great help.

Thanks for your time.

Capt'n

 

[Jeff Boyce]

I'll recommend that you spend a little time looking at Access HELP on the
topic of "Totals queries". The approach I suggested will make a lot more
sense if you have a sense of how to create this kind of query.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Capt'n]

thanks.