Referincing Fields

[Kat]

I am importing data into a table and the design is terrible but as I don't
have access to the original source so I am stuck with it. Each record is for
a single associate working in a certain path on a particular day so my table
ranges in the 10s of thousands. Each path has a unique combination of
process and function names with units rolling up under several different
fields named according to the type of unit it is counting, such as StowCases,
PickPallets or PickEACH, but I am only interested in using one of the fields
depending on the path and there are about 50 of these types of fields I am
trying to wade through. Because the paths have units in multiple fields I
can't just add them. Is there a way in SQL or using the interface to create
a query that will create a single units field in my query that will reference
a different field within the imported table depending on the process and
function names in that particular record. I want to avoid using an iff
statement as I am trying to make this available to several different sites
across our network and they may need to add or remove paths which would be a
lot easier with a seperate table with a relationship to the import rather
than going in and trying to edit a nested Iff statement(I currently have 21
different paths that I need to evaluate).
I have a table is set up that has the process name, function name and the
field/unit name that is in the imported table.

 

[Jeff Boyce]

Kat

I'm not sure I understand enough about what you have and what you want to do
to offer any specific suggestions.

Please note that you are not limited to continuing to use the existing data
store if it makes what you want to do more difficult. Have you considered
creating a new Access application and "pointing" at the existing data for a
starting point?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kat]

I am open to your idea but I have no idea how I would manipulate the data as
it is not as simple as combining all, or even select, fields.
Current State - The fields are date, employee ID, employee name, process
name, function name, hours, BulkEachPack, CaseReceiveEach, CaseRecevieCase,
CaseReceivePallet, ItemPickedEach, ItemPickedPallet, ItemPickedCase(This goes
on for another 30 to 40 fields)ItemPickedEach is an example of a type of unit
that has been processed by a particular tool. For Pick Path A I am only
going to look at units under ItemPickedEach even though there are also units
under ItemPickedCase which the units I want for pickpathB. I also have a
table that lists Path(ex MultisPick), Process Name(ex Picking), Function
Name(PathA) and unit/field name(ex ItemPickedCase). I want a query where the
process name and function names in both tables are related. In excel I would
write it for Path A offset so many rows. In access I want to say when
process name is picking and function name is path A pull data from the field
ItemPickedCase. If Process name is picking and function name is pathB pull
data for the field ItemPickedEach. End result would have fields date,
employee ID, name, process name, function name, hours and Units. I could
try to do a crazy ridiculous nested function but as tools change function
names change which means going in and editing the expression in addition to
adjusting it for another site would not be very user friendly as we have
several paths that are different.

 

[Jeff Boyce]

Kat

As you've described, this sounds very much like how the data would be
structured ... if it were being used in a spreadsheet!

A common approach to getting better use out of Access' relationally-oriented
features and functions is to accept that the incoming data is "raw". Spend
the time to diagram out your relational table structure (entities and
relationships). Then create queries that can 'parse' your raw data into
your "final" table structure (which you've normalized).

That way, the queries you create and the reports you generate can be based
on that set of well-normalized data, and take advantage of Access' features.
Otherwise, both you and Access will have to be working overtime to try to
find work-arounds for 'sheet data.

You CAN drive nails with a chainsaw, but it isn't a very good idea...

Regards

Jeff Boyce
Microsoft Office/Access MVP