Need to Alter My Query

[FerryMary]

In an attempt to not store a calculated result I need to alter the following
query so all records are reflected. Guidance,f rom MVP Jeff, was alternative
to storing a calculation, by using combobox at top of form. I like this,
since everything I read says not to store one. But since my query is based
on "Max" it returns wrong value for older records. Following is what I have
and what I want:

What I Have:
The following Query produces the ReportNo that I prefer, but only only on
max records. When I try to alter it to show all records I wind up with a
result of 1 in each case. (I think I need a running count, but just can't
get it)

SELECT Max(tblJob.JobNumber) As MaxOfJobNumber,
Format(tblJob.VesselID,"000") &
Format(tblJob.InitiatedDate,"yy") &
Format(tblStatus.JobType) &
Format(Count(tblStatus.Jobtpe),"0000") As ReportNo,tblJob.VesselID
FROM tblStatus INNER JOIN tblJob ON tblStatus.StatusED
GROUP BY tblJob.Vessel,(tblStatus.JobType),Format(tblJob.VesselID,"000") &
Format(tblJob.InitiateDate,"yy")
ORDER BY Max(tblJob.JobNumber) DESC;

That query is an alteration of query worked out for me by Chris2(another
generous soul in this group) It produces my report #
"VesselID""yy""JobType""Count"
Sample result: 06905SCI0005


What I Want:
The report# result for each record maintaining the count per Vessel/yy/JobType
ex.
JobNo ReportNo VslID YY JobType Count
1 00603SCI0001 006 03 SCI 0001
2 00605FSY0001 006 05 FSY 0001
3 00605SCI0001 006 05 SCI 0001
4 00605FSY0002 006 05 FSY 0002

I've looked at If/Then,Where,Having and just have missed something. Any
help or suggestion is very appreciated.
FerryMary

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not real sure I understand you're table designs, or what you're
really trying to do, but here's a stab:

SELECT J.JobNumber,
Format(VslID,"000") & Format(YY,"00") & JobType &
Format(JobCount,"0000") As ReportNo,
VslID, YY, JobType, Count(*) As JobCount,

FROM tblStatus As S INNER JOIN tblJob As J
ON S.StatusID = J.StatusID

GROUP BY J.JobNumber,
Format(VslID,"000") & Format(YY,"00") & JobType &
Format(JobCount,"0000") As ReportNo,
VslID, YY, JobType

ORDER BY J.JobNumber


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk2jBoechKqOuFEgEQITQwCeIy9QSlAxeutu+NoDtBRX7F4xelYAoPmx
3gTus/wSUP5agoCpaE1qHRbS
=MZ4j
-----END PGP SIGNATURE-----

 

[FerryMary]

tbls involved:
tblJob(pk-JobNumber=AutoNumber),fields in tblJob needed for desired
recordset VesselID(fk)-Integer,InitiatedDate-shortdate,StatusID(fk)

tblStatus-JobType-text

I want a cumulative count of jobs by
vessel-date(as "yy")-JobType(three letter code)-count(incremental where
three preceding elements are same) I wish I could explain it better.

Thanks-I'm working on your suggestion, but I am failing so far. :-(
Mary-may be just trying too hard

 

[FerryMary]

Oops I left out important peice of info.

I also need to show all JobNumbers and what each new recordset is on row
next to JobNumber