Need year to date record count,:warning veritable child in Access

[Guest]

Am attempting to number reports using a three field combination.

1st field three digit vessel ID (used an Asset table for listing fleet (I'm
in marine maintenance)-figured out by myself

2nd field two digit current year (used date box with validation rule

DateAdd("yy",-1,Date())-got that thanks to this group :Thanks Rick Hoping

the Validation Rule was correct entry point for this expression.

3rd field I want it to give me a report count based on year to date reports
per vesselID (AssetID)

Following is lengthy and probably unnecessary:

I like this system because it is easy for my guys on the various vessels to
inquire about work requests on their vessels,it's useful in established
retention/disposition guidelines and I was using it manually on a spreadsheet
when my tracking system was just being used locally. So there are already
quite a few comfortable with this tracking system Now it is hoped we can use
it system wide. I don't think replication will be a problem as most
facilities primarily work on select vessels in the fleet(I doubt more than
one user would be accessing an asset -I hope to be able to lock asset records
in that regard to allow only one user at a time, anyway.)

Sorry this was so lengthy, but I noticed the smart guys often asked for more
info. Please forgive my obvious lack of understanding, would continue trying
to find answer on my own-but the hounds are baying at the door.

 

[Guest]

thank you so much, and on a Sundy too! It's doubtful my per vessel work
orders would exceed 1000 in a year (any boat with that many problems should
be turned into a reef) so I can just alter "0000" to "000" or better to just
leave as you indicated and not worry about the extra digits??

Mary

 

[Chris2]

Am attempting to number reports using a three field combination.

1st field three digit vessel ID (used an Asset table for listing fleet (I'm
in marine maintenance)-figured out by myself

2nd field two digit current year (used date box with validation rule Hoping
the Validation Rule was correct entry point for this expression.

3rd field I want it to give me a report count based on year to date reports
per vesselID (AssetID)

FerryMary,

You can paste the following create-table SQL into Access Queries that
are opened in SQL View (menus VIEW > SQL View), and then executed.
You can then open the resulting tables in Design View to see what
happened. You can then load in the sample data into the tables
(there's not much, it can be done by copying and pasting), and then
use the SELECT SQL to check the results I got. It will all,
undoubtedly, need to be modified for your situation.


Making some assumptions:

CREATE TABLE Vessels
(VesselID CHAR(3)
,NameVessel TEXT(144)
,CONSTRAINT pk_Vessels PRIMARY KEY (VesselID)
)

Sample Data
VesselID NameVessel
AAA Big Oil Tanker
BBB Little Skiff

CREATE TABLE VesselReports
(VesselReportID AUTOINCREMENT
,VesselID CHAR(3) NOT NULL
,DateReport DATETIME NOT NULL
,CONSTRAINT pk_VesselReports PRIMARY KEY (VesselReportID)
,CONSTRAINT fk_VesselReports_Vessels FOREIGN KEY (VesselID)
REFERENCES Vessels (VesselID)
)

Sample Data
VesselID DateReport
AAA 12/01/04
AAA 01/01/05
AAA 01/02/05
BBB 01/05/05
BBB 01/05/05
BBB 01/10/05


To get "a report count based on year to date reports per vesselID":

Note: No specification is made for how many digits to display in
"field 3", so I chose 4, for an annual limit of 9999 reports.

Expected Results:
AAA050002
BBB050003


SELECT V1.NameVessel
,V1.VesselID &
FORMAT(VR1.DateReport, "yy") &
FORMAT(COUNT(VR1.DateReport), "0000") AS ReportInfo
FROM Vessels AS V1
INNER JOIN
VesselReports AS VR1
ON V1.VesselID = VR1.VesselID
WHERE Year(VR1.DateReport) = Year(Date())
GROUP BY V1.NameVessel
,V1.VesselID
,V1.VesselID &
FORMAT(VR1.DateReport, "yy")


Results
Big Oil Tanker AAA050002
Little Skiff BBB050003

That appears to work nicely.


Sincerely,

Chris O.

 

[Chris2]

SELECT V1.NameVessel

,V1.VesselID &
FORMAT(VR1.DateReport, "yy") &
FORMAT(COUNT(VR1.DateReport), "0000") AS ReportInfo
FROM Vessels AS V1
INNER JOIN
VesselReports AS VR1
ON V1.VesselID = VR1.VesselID
WHERE Year(VR1.DateReport) = Year(Date())
GROUP BY V1.NameVessel
,V1.VesselID
,V1.VesselID &
FORMAT(VR1.DateReport, "yy")

There is a one-line typo above.

Please remove the third to last line, so that it appears:

SELECT V1.NameVessel
,V1.VesselID &
FORMAT(VR1.DateReport, "yy") &
FORMAT(COUNT(VR1.DateReport), "0000") AS ReportInfo
FROM Vessels AS V1
INNER JOIN
VesselReports AS VR1
ON V1.VesselID = VR1.VesselID
WHERE Year(VR1.DateReport) = Year(Date())
GROUP BY V1.NameVessel
,V1.VesselID &
FORMAT(VR1.DateReport, "yy")


Sincerely,

Chris O.

 

[Chris2]

thank you so much, and on a Sundy too! It's doubtful my per vessel work
orders would exceed 1000 in a year (any boat with that many problems should
be turned into a reef) so I can just alter "0000" to "000" or better to just
leave as you indicated and not worry about the extra digits??

Mary

Mary,

You're welcome.

And by all means, modify the code to suit.

Change "0000" to "000" in this following line:

FORMAT(COUNT(VR1.DateReport), "0000") AS ReportInfo


Sincerely,

Chris O.

 

[Guest]

Chris2,

you've been fantastic, everything has worked swimmingly, thanks again! I so
hate to ask another question but here goes. (It's so frustrating to be so
close to having this project exactly like I want it.)

regarding your query that follows, since my vessel IDs are numerical can I
show the result of query with dashes: Would I add that to the query? or can
formatting the textbox 000-00-000 (vesselID-2yeardate-report#) do the trick?
Also, would a textbox on my form with =DLookup("fieldinquery", "queryname")
show the query result on the form?

Thank you seems so inadequate
Mary

 

[Chris2]

Chris2,

you've been fantastic, everything has worked swimmingly, thanks again! I so
hate to ask another question but here goes. (It's so frustrating to be so
close to having this project exactly like I want it.)

regarding your query that follows, since my vessel IDs are numerical can I
show the result of query with dashes: Would I add that to the query? or can
formatting the textbox 000-00-000 (vesselID-2yeardate-report#) do the trick?
Also, would a textbox on my form with =DLookup("fieldinquery", "queryname")
show the query result on the form?

Thank you seems so inadequate
Mary

Mary,

Sorry for the lengthy time before I've replied.

In case you haven't already figured it out.

SELECT V1.NameVessel, V1.VesselID & "-" &
FORMAT(VR1.DateReport, "yy") & "-" &
FORMAT(COUNT(VR1.DateReport), "0000") AS ReportInfo
FROM Vessels AS V1 INNER JOIN VesselReports AS VR1
ON V1.VesselID = VR1.VesselID
WHERE Year(VR1.DateReport) = Year(Date())
GROUP BY V1.NameVessel
,V1.VesselID & "-" &
FORMAT(VR1.DateReport, "yy") & "-";


Sincerely,

Chris O.

 

[Guest]

Everything you've offered has worked exactly as desired. thanks again

FerryMary