|Subject:||Seemingly complex task requiring your help|
|Posted by:||noodnutt (noodnu…@gmail.com)|
|Date:||Sat, 12 May 2018|
In-as-much as I have been tinkering with Access for some time now, I don't =
do anywhere near enough to call myself remotely proficient.
With that said, I read a long time ago ( In my Microsoft A2K reference manu=
al ) that Access works so much better with numeric identifiers for just abo=
In my very early days feeling around in the dark I made the newbie mistake =
of creating alpha/alphanumeric fields, and as a consequence learnt from the=
lag it generates with nested queries.
I now preference to generate queries "On-the-Fly", but that's for another d=
iscussion thread at a later date ;-).
To the meaty bit of this post:
Essentially, in this new project, one of the imported files' data is predom=
inantly text-based ( with the exception of the Date & 3 Number cells ) and =
rather than keep the offending fields/cells exclusively text-based, I would=
like to replace anything that is repeated with a matching ID No for speed =
& rationale from the table created with their respective ID's in place.
I was thinking along the lines of doing an Update Query:
Prior to the update firing do a DLookup of the specific text-based fields a=
nd copying their matching ID Number into the ( FieldTo ) field instead of t=
he actual text.
The 6 fields above can all be converted to a numeric identifier, I just nee=
d to nut the correct structure for it to happen.
Just as a side note, some of the "TO_CARRIER" text values are N/A just on t=
he off-hand it throws an error.
I could always change tblCarriers.CarrierID field in tblCarriers from Auto =
to Number and just assign ( 0 ) zero as N/A.
I was thinking, any new carriers added can be incremented via a BeforeUpdat=
e(), something like:
Me.CarrierID.Value =3D Me.CarrierID + 1
If anyone has something that can get me started please, that would be aweso=
As always, much appreciated and many thanks.