help with Query?

  • Thread starter Thread starter Capt'n
  • Start date Start date
C

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));
 
If you have 'many' rows per Asset, which one do you want?

Regards

Jeff Boyce
<Office/Access MVP>
 
Jeff,
I need the one that has the latest "maintenancedate". This table is
indexed by AssetID asc, maintenancedate Desc

Thanks,

Capt'n
 
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>
 
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
 
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>
 
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
 
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>
 
Back
Top