Seemingly complex task requiring your help

Giganews Newsgroups
Subject: Seemingly complex task requiring your help
Posted by:  noodnutt (noodnu…@gmail.com)
Date: Sat, 12 May 2018

Hi Team

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=
ut everything.

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:

From: Import_Outbound
To: Convert_Outbound

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.

tblImport_Outbound

FieldFrom------------------FieldTo~~~~~~~~~~~~TableValue_Lookup.
TO_CARRIER------UpdatesTo..lnfInboundCarrier~~tblCarriers
FRM_STE---------UpdatesTo..lnfStateFrom~~~~~~~tblState
TOCITY----------UpdatesTo..lnfDest~~~~~~~~~~~~tblDest
STE-------------UpdatesTo..lnfStateTo~~~~~~~~~tblState
CARRIER---------UpdatesTo..lnfOutboundCarrier~tblCarriers
ORDER_COMMODITY-UpdatesTo..lnfCommodity~~~~~~~tblCommodity

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=
me.

As always, much appreciated and many thanks.

Kind regards
Mark.

Replies