Number/Letter Combinations as Entries

[AWoodward]

Good Morning All!



I searched the forum before posting so please forgive me if this has been asked and answered. I am creating an Access application that we will use to generate, track and report on transmittals. We are a construction company. Each transmittal consists of a list of the blueprint sheets that are included.

We must keep a record of all sheets that are sent out and received. At the moment, they create a transmittal sheet in word and then enter the sheets list again into Excel as a drawing log. I am creating an Access database that will do both, however, I haven't been able to figure out how to track the number/letter combinations or have access recognize that E1:E20 includes all sheets E1 through E20. I appreciate any help! Thank you!

[orange]

I recommend you build a data model of your proposed application. Here is a link to Barry Williams' site that highlights the steps involved.
There are several related articles in the Database Planning and Design link in my signature. Good luck with your project.

[kd2017]

I know drawing number formats are different for every architect and engineer. In my [sheets] table I would have a separate numeric sort order field (the user doesn't need to see) that I could query against to collect ranges of sheets. For example sheet E04 might have an arbitrary sort order number of 31 and sheet E21 might have a number of 120, E04:E21 equals all sheets between sort order numbers 31 and 120 (again actual numbers are arbitrary)... This way you don't have to try and parse sheet number strings like ME01-02C for example.

You will still need to use vba to parse your sheet range notation ie E1:E20, but this should be easy. Check out the split() function, split the string by the colon then lookup the sheet's [numeric sort numbers] by the [sheet number strings], then simply query for all sheets between [numeric sort numbers].

I'm curious, are 'ME' mechanical drawings?

If I've only managed to confuse or muddy the waters I can whip up an example db to illustrate.

[AWoodward]

Thank you both for the help! kd2017 that's perfect!!!! I don't know why I didn't think of that other than I was over thinking it! Thank you so much!! You saved me from another night of Accessing in my sleep. A

[kd2017]

No problem! I understand, I've literally woken up at 4am in recent weeks thinking about modeling a tracking system for construction docs, revisions, and takeoffs... Revisions can be a nightmare

[AWoodward]

I forgot to answer the other part of your post! We are a structural steel company. Revisions are the bane of my existence! LOL So our sheet number work like this...E would be an erection sheet, EM would be Embeds, ME would be misc. erections (like stairs, ladders etc), no letter before a number is a detail sheet, AB is anchor bolts. Then we have RFIs, ASIs, CCDs to track as well. If I ever get the thing built I'll send you what I came up with. Sounds like we are thinking the same thing at 4am! LOL

[kd2017]

Sure I'd like to check it out. Good luck with your project!