Re: used sql stored in field

Okay... I see, I will try to describe what the purpose is of this excercice.

I am trying to write a tool to identify the inconsistencies in some ERP-
software. In an attempt to make the tool easy to maintain and grasp by others
I want to create a rule-based tool. So I don't want to put the identification
in the query itself but in different table which will represent the rules.

In table 1 I define whether a SKU (stock-keeping Unit , supply chain object)
is from a Plant, central Warehouse or a local Warehouse.. I do this by
looking at a combination of 4 fields: Location, source code, distribution
source code and replenType. Each combination I validate manually and check
whether it is a Plant, central wh or a local one. (these four fields are
present in table SKUGENERAL)

Some the fields in the ERP-software are dependent on LocType, others are just
default.

Therefore I created 2 tables: table 2 where I define the locType -dependent
fields and table 3 for the locType independent fields.

Table 2

LocType Priority Allocation Calendat ..... ....... (table
header)
Plant 1 .....
(record 1)
Centralwh 2 ......
(record 2)
local wh 3 ...
(record 3)

As you see I only need three records. However, now comes the problem, for
example field 'Allocation Calendar is also dependent on Location.

This field needs to be filled in in the following way: "EUALL" + Loc of the
SKU
I, however, don't want to add Location in this table because then it would
become too large due to the many possibilites (100 locations with three
possible types....).

Therefore I was thinking by just putting as value "EUALL" + [SKUGENERAL]![Loc]
.. Loc is stored in this the SKUGENERAL table. In this way it is clear how
this field needs to be populated for a plant for everyone.

Next I want to use that field in combination with SKUGENERAL table and table
1 to define for every plant its Allocation callendar based on the rule in
table2.

Question: How can I Use the code stored as a value (string) in the table 2 to
do this? So without retyping the formula in the query what would make
maintenance impossible...

I hope this makes any sense....








Jeff Boyce wrote:
Sorry, I understand less than I did before!

Perhaps you could use a pair of queries to generate the codes, then use a
third query to combine them.

You've described a "how" ... as in how you are trying to do something.

If you'll describe a bit more of the "what" ... what you want to do, not how
... the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

the quotes around "EUALL" + []![loc] was just to indicate that access sees
this what is stored in the field as a string
[quoted text clipped - 41 lines]
thx
Hendry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200804/1

.

Relevant Pages

  • Re: used sql stored in field
    ... LocType Priority Allocation Calendat ... is from a Plant, central Warehouse or a local Warehouse.. ... looking at a combination of 4 fields: Location, source code, distribution ...
    (microsoft.public.access.forms)
  • Re: used sql stored in field
    ... You are not required to STORE the combination in order to be able to use it. ... And you can use queries based on other queries if you need to compare. ... is from a Plant, central Warehouse or a local Warehouse.. ... looking at a combination of 4 fields: Location, source code, distribution ...
    (microsoft.public.access.forms)
  • Re: Struts - Getter method
    ... group loves source code, as a rule. ... posting the code then. ... but it's not a collection of type Book. ... Query, while creating ...
    (comp.lang.java.programmer)
  • Re: Struts - Getter method
    ... group loves source code, as a rule. ... posting the code then. ... Query, while creating ... instances of type Book and setting the attributes of each instance ...
    (comp.lang.java.programmer)
  • Re: How do you Export Data from Access into Multiple Tabs in Excel from one query
    ... [Plant 01 Raw Warehouse Flow Parts].Date, ... Turn the query into a parameter query that gets the date the user enters ... Then it's just a matter of entering the date and worksheet name on the ...
    (microsoft.public.access.externaldata)