Queries Automation

[DiDi]

I have plant-fertilizer-season Table, where applied fertilizers to
plant is different for seasons, fert1 is main fertilizer used, fert2,
fert3, fert4 would be for the secondary, auxiliary used, below is a few
records for reference:

Tab_MPlant:
ID Plant fert1 fert2 fert3 fert4 Season
1 P1 S1 S3 S2 F
2 P1 S4 S2 S1 S3 S
3 P2 S1 S3 S4 F
4 P2 S2 S1 S3 S4 S

By introducing a fert_ID and using Make Table queries and Append
queries I can finally come up the following Table:

Tab_SPlant:
ID Plant fert Fert_ID Season
1 P1 S1 1 F
1 P1 S3 2 F
1 P1 S2 3 F
2 P1 S4 1 S
2 P1 S2 2 S
2 P1 S1 3 S
2 P1 S3 4 S
3 P2 S1 1 F
3 P2 S3 2 F
3 P2 S4 3 F
4 P2 S2 1 S
4 P2 S1 2 S
4 P2 S3 3 S
4 P2 S4 4 S

However, when the plant inventory grows, a manually manipulate queries
become very cumbersome. How to make the follow process becomes
automation within Access?

SELECT Tab_Mplant.ID, Tab_Mplant.plant, Tab_Mplant.fert1 AS Fert, 1 AS
fert_ID, Tab_Mplant.season INTO Tab_f1
FROM Tab_Mplant
WHERE (((Tab_Mplant.fert1) Is Not Null));

SELECT Tab_Mplant.ID, Tab_Mplant.plant, Tab_Mplant.fert2 AS Fert, 2 AS
fert_ID, Tab_Mplant.season INTO Tab_f2
FROM Tab_Mplant
WHERE (((Tab_Mplant.fert2) Is Not Null));

SELECT Tab_Mplant.ID, Tab_Mplant.plant, Tab_Mplant.fert3 AS Fert, 3 AS
fert_ID, Tab_Mplant.season INTO Tab_f3
FROM Tab_Mplant
WHERE (((Tab_Mplant.fert3) Is Not Null));

SELECT Tab_Mplant.ID, Tab_Mplant.plant, Tab_Mplant.fert4 AS Fert, 4 AS
fert_ID, Tab_Mplant.season INTO Tab_f4
FROM Tab_Mplant
WHERE (((Tab_Mplant.fert4) Is Not Null));


INSERT INTO Tab_SPlant ( ID, plant, Fert, fert_ID, season )
SELECT Tab_f1.ID, Tab_f1.plant, Tab_f1.Fert, Tab_f1.fert_ID,
Tab_f1.season
FROM Tab_f1;

INSERT INTO Tab_SPlant ( ID, plant, Fert, fert_ID, season )
SELECT Tab_f2.ID, Tab_f2.plant, Tab_f2.Fert, Tab_f2.fert_ID,
Tab_f2.season
FROM Tab_f2;

INSERT INTO Tab_SPlant ( ID, plant, Fert, fert_ID, season )
SELECT Tab_f3.ID, Tab_f3.plant, Tab_f3.Fert, Tab_f3.fert_ID,
Tab_f3.season
FROM Tab_f3;

INSERT INTO Tab_SPlant ( ID, plant, Fert, fert_ID, season )
SELECT Tab_f4.ID, Tab_f4.plant, Tab_f4.Fert, Tab_f4.fert_ID,
Tab_f2.season
FROM Tab_f4;

 

[Duane Hookom]

It sounds like you are maintaining the Tab_MPlant and Tab_SPlant. Is there a
reason for continuing to use Tab_MPlant? If so, you should be able to create
a union query similar to your select queries. Use this union query as a
source for an append query to add records to Tab_SPlant. You should have a
unique index in Tab_SPlant to prohibit the addition of duplicate records.

 

[Amy Blankenship]

I have plant-fertilizer-season Table, where applied fertilizers to
plant is different for seasons, fert1 is main fertilizer used, fert2,
fert3, fert4 would be for the secondary, auxiliary used, below is a few
records for reference:

Tab_MPlant:
ID Plant fert1 fert2 fert3 fert4 Season
1 P1 S1 S3 S2 F
2 P1 S4 S2 S1 S3 S
3 P2 S1 S3 S4 F
4 P2 S2 S1 S3 S4 S

By introducing a fert_ID and using Make Table queries and Append
queries I can finally come up the following Table:

Tab_SPlant:
ID Plant fert Fert_ID Season
1 P1 S1 1 F
1 P1 S3 2 F
1 P1 S2 3 F
2 P1 S4 1 S
2 P1 S2 2 S
2 P1 S1 3 S
2 P1 S3 4 S
3 P2 S1 1 F
3 P2 S3 2 F
3 P2 S4 3 F
4 P2 S2 1 S
4 P2 S1 2 S
4 P2 S3 3 S
4 P2 S4 4 S

Your problem is your table design.

Try something like this

tblPlants
PlantID
PlantDesc

tblPlantSeasons
PlantSeasonID
PlantID
Season
SeasonOrder

tblFertilizers
FertilizerID
FertilizerDesc

tblPlantSeasonFertilizer
PlantSeasonID
FertilizerID
Priority

Now you just need one query

Select PlantDesc, FertilizerDesc, Season FROM
tblPlants INNER JOIN
(tblPlantSeasons INNER JOIN
(tblPlantSeasonFertilizer INNER JOIN
tblFertilizer ON tblPlantSeasonFertilizer.FertilizerID =
tblFertilizer.FertilizerID)
ON tblPlantSeasons.PlantSeasonID =
tblPlantSeasonFertilizer.PlantSeasonID)
ON tblPlants.PlantID = tblPlantSeasons.PlantID
ORDER BY SeasonOrder, PlantDesc, Priority;

HTH;

Amy

 

[DiDi]

Thank you for suggested solutions. However, I got the Tab_Mplant from
the nusery group, I have to normalized the table to be able to do Forms
and Reports to give them various information.., so I am looking a way
to automat my process..

 

[Amy Blankenship]

I have some suggestions for you, but first I want to ask what you're
planning to do when the database starts running across multiple years.

-Amy

 

[DiDi]

I know the way you suggested is more relational in terms of
"relationships", I have talked to that group about the changes,
however, they still do whatever they have been doing, I tried not to
alter unless get their concurrence. It is not easy and is a long
process, when someone has mde up his mind, you know..

If you have a new suggestions, I am glad to hear from you.

 

[Amy Blankenship]

What they do shouldn't have much impact on what you do. You can take the
import information and reformat it as needed. A bad data structure doesn't
serve anyone long term. It will cause 2-3 times the work over time.
Sometimes taking a stand now can pay off big time if that's what you need to
do. However, if it's just an import you can do what you want.

To make it where it's going to be robust long term, I can't give you a
solution until I know how your table structure is going to handle multiple
years. As it stands, the spring of 2007 will lump together with spring
2006, for example, with no way to tell the difference. There's little point
in giving you a solution that solves your immediate problem without looking
to the future and preventing new ones.

HTH;

Amy