help needed with writing a bit of code

[Karen]

Here's the setup: Access 2007 (but the app is in 2002 - 2003 file
format). Split database. Table 1 is Reagents and has a code and a
producttype (other fields not relevant here). Table 2 is
Ingredients. It has a code field that is the key to link to Reagents
(one to many). There is also a RMcode field that is the code for the
raw material that is an ingredient of the Reagent.

Some of the ingredients are Pre-mixes and a Reagent can have
ingredients that include several Pre-Mixes. Premixes have their own
ingredients (they are included in the Reagents Table and their
components are in the Ingredients Table). Reagents that have this
structure have a ProductType of 'X' and Premixes have a producttype of
'Premix'.

An example follows:

Table Code Desc ProductType
Reagent 5555 Special Reagent X

RMCode RMDesc
Ingredients 5555PM-A Premix A for Special Reagent
5555PM-B Premix B for Speical Reagent
25789 XYZ Chemical
25412 RST Chemical

Code Desc
ProductType
Reagent 5555PM-A Premix A for Special Reagent Premix

Ingredients 25963 HIJ Chemical
25123 MNO Chemical
25741 ABC Chemical


Problem: I want to be able to enter in a Reagent code on a form and
have a bit of code that would print the ingredients list for the
Reagent 5555 and then print the ingredients list for the Premixes
5555PM-A and 5555PM-B.

I have a form that prints a single level ingredients list. I just
need to figure out how to print that single level ingredients list and
then turn around and print another if there's an ingredient that is a
Premix (by virtue of the producttype).

any help would be appreciated.

Karen

 

[Karen]

Hi Karen,

We can best help you if you post the design of your tables.

Steve
(e-mail address removed)
















- Show quoted text -

Table 1: Name: Reagents
Fields Code, Text 15 characters
ProductType, Text 15 characters

Table 2: Name: Ingredients
Fields Code, Text 15 characters
RMcode, Text 15 characters

They are related by the Code Fields.


I don't know what else you need. these are the only relevant fields
in the two tables.

 

[Bob Quintal]

Hi Karen,

ignore Steve's advice, he's just confused and trying to confuse you.

All you need are two tables, one should list all items, reagents,
premixes and Raw materials. along with a code or id number, and you
can include a field which identifies the type of item (reagent,
premix, or raw material) although this isn't required for your report
and a links table which contains only the id of the parent ,the id of
the constituent part, and the quantity used to make one batch of the
mix so if a recipe contains 5 items the parent number appears 5
times, once for each constituent item.

Then you just use the query builder to set up a query.

The query contains two, three or more instances of the links table,
dependent on how deeply nested you need to go to get all your raw
materials.

So use the show tables dialog in the builder to add the links table 3
times. Bring up the properties dialog and set the aliases to
"Level0" for the first, "Level1" for the next, etc.

Join each instance of the table to the one on its right by dragging
the child ID of the first to the parent id of the next higher level..
Change the join type to whichever option says All of Level0 and only
matching ID in level1, repeat until you have joined all levels.

Now you need to insert three calculated fields.These are a set of
nested statements.

The first is
Constituent: nz(level3.child,nz(level2.child,nz
(level1.child,level0.child)))
The second is
Qty: nz(level3.qty,nz(level2.qty,nz(level1.qty,level0.qty)))

And the third is
LEVEL: iif(isnull(level1.child),1,iif(isnull(level2.child),2,3)

just replicate the nesting for as many levels as you need
Save this query.
Create a new query that uses this one plus the items table to return
item names descriptions and quantities.

Use this query for your report.



m: