Using 1 form entry to make multiple record entries

[LXIX]

Good Morning,



New Access user here, please forgive my ignorance. I need some assistance please. I have built a database for my nursery, I have 27 beds that each contain 18 plants. I have each plant barcoded and each bed barcoded with individual plant/bed numbers. What I'd like to do if possible is associate the plants in the bed under 1 identifier so that if I scan the bed bar code it will show that all the plants had that procedure done. Also there are tasks that I do to the entire greenhouse (i.e. water) that I'd like to be able to mark all plants as being done. I have 2 tables, one with the plant numbers and the second with the actions that need to be taken (a relationship between the 2 linked by "Plant ID". I have a form I created for data entry to make things more user friendly for my staff to document the actions they take.

Here's what I am trying to do:

Bed 1 contains plants numbered 1-18. In the Plant ID field, I'd like to use an identifier like "BED 1", complete the form, and it would put that entry into plants 1-18 so 1 don't have to do 18 entries per bed.

I want to build a hierarchy like this:
An identifier like "ALL" that would mark all plants in all beds.
An identifier like "BED 1" that marks all plants in that bed.
If I need to do something to just a specific plant I can just scan that plant's code (which is where I am now).

I'm not exactly sure what this process I'm trying to do is called. I'm more than willing to watch Youtube videos if someone can help me identify what to search for.

Thanks in advance for your assistance.

[Micron]

You don't have enough tables. A bed is an entity, a plant in that bed is a child (thus another entity) of that bed. That's 2 tables. If you have more than one greenhouse that's another table. Then your task table and probably a junction table since I assume you might have a many to many situation (many plants can have many tasks performed on/for them). That would make 4 or 5 tables, depending on the greenhouse table.

Have to leave now so I'll leave it to others to chime in as well.

[June7]

Only two tables in database?

Can certainly use SQL and VBA to "batch" edit or create records.

[LXIX]

I apologize for being vague above, hopefully this is a better explanation. I have 1 greenhouse, which contains 27 beds, which contain 18 plants each. Plants stay in each bed for their entire lifecycle, so they do not get moved between beds.

[mike60smart]

Hi
Welcome to the Forum.
As Micron has said you need more tables to manage your process.

Did you manage this process in Excel before moving to Access?

If you did can you upload a copy of the Excel file?

[June7]

If plants table has a field that identifies the bed where each plant is located, user input into an unbound textbox or combobox can be used as criteria in an SQL action.

What exactly do you mean by "mark" each plant? We need to know more about table structures and your business procedures. Suggest you provide db for analysis. Follow instructions at bottom of my post

[LXIX]

Here is the DB I'm working with.


Greenhouse DB.zip

[LXIX]

I did not, I did everything in Access.

[mike60smart]

Hi

Observations so far.
1. Plant List Table
Plant List table name should be renamed to tblPlantList
Current Plant List PK is a Text DataType - remove the PK from this field and rename the field to PlantNo
Add a new PK named PlantListID - Autonumber - LongInteger
You should not have any spaces in field names.
You should not use any special characters ie &/()*&^%$£"! in field names

2. Action List
Rename the table to tblActionList
The current ID Primary Key should be renamed to ActionListID
The Plant ID field should be renamed PlantNo
Add a new field named PlantListID - Foreign Key and make it a Number - LongInteger Data Type
No spaces in field names

Then to make the two tables Relational you join the PK Autonumber from tblPlantList to the FK in tblActionList and also Enforce Referential Integrity.

[ArviLaanemets]

On fly.

You need tables like
tblPlantSpecies: PlantSpaciesID, PlantName, ... (a table where you register all plant species you have (a row for every one, and you can have additional fields for any needed info for given species which doesn't ever change);
tblGreenhouses: GreenouseID, [GreengouseName], ... (you can skip this table in case you have one, but having it doesn't add a lot o work, and in case hou have more of them in future, you don't need to redesign the whole database);
tblGreenhouseBeds: GreenhouseBedID, GreenhouseID, [Comment], ...;
tblBedPositions: BedPositionID, GreenhouseBedID, Position (determines the position of specific plant in specific greenhouse bed);
tblSeasons: Season (Probably you will use your greenouse(s) for more than a single season, and probably the plant species in given location of given greenhouse bed can differ in every season);
tblSeasonBedPlant: SeasonBedPlantID, Season, GreenhousebedID, BedPositionID, PlantSpeciesID;
tblTasks: TaskID, TaskDescription, TaskTyp, ... (TaksTyp indicates, is the task meant for entire greenhouse, for entire greenhouse bed, for specific plant specie, or for specific plant)
tblSeasonGreenhouseTasks: SeasonGreenhouseTaskID, Season, TaskID, TaskDate, ..., TaskSatus (The schedule of tasks designed for whole greenhouse, and the status of them);
tblSeasonBedTasks: SeasonBedTaskID, Season, GreenhouseBedID, TaskID, TaskDate, ..., TaskStatus (The schedule of tasks designed for whole greenhouse bed, and the status of them);
tblSeasonBedSpeciesTasks: SeasonSpeciesTaskID, Season, GreenhouseBedID, PlantSpeciesID, TaskID, TaskDate, ..., TaskStatus (The schedule of tasks designed for specific species of plants in given greenhouse bed, and the status of them);
tblSeasonBedPlantTasks: SeasonBedPlantTaskID, SeasonBedPlantID, TaskID, TaskDate, ..., TaskStatus (The schedule of tasks designed for specific plants in given greenhouse bed position, and the status of them)

[June7]

Not sure your situation calls for a design as elaborate as Arvil suggests. If you have one greenhouse and only one species and this will never change, stick with what you have, mostly. Fields Action Date, Plant Inspection, Action Taken in Plant List seem unnecessary.

Strongly advise to remove spaces from naming convention. Also, don't use any symbols in names with exception of underscore, so names should be like PlantID or Plant_ID. Remove hyphen from form name.

Now, as to how to 'batch' add multiple records, add an unbound control (combobox?) to choose bed to your form. The real trick is figuring out what event to put code into. Do you want user to click a button to trigger this? Could have code like:

If Not IsNull(Me.cbxBed) Then
CurrentDb.Execute "INSERT INTO ActionList(PlantID, ActionDate) SELECT PlantID, Date() FROM PlantList WHERE BedLocation='" & Me.cbxBeds & "'"
End If

Set PlantID and ActionDate in ActionList as compound index and this will prevent duplicate pairs, unless you want to allow multiple entries for each plant on each date.

[LXIX]

I have already addressed the species issue in the plant numbering system. Thats why there are 2 numbers separated by a hyphen (03-01), the first number is the species, followed by the plant number of that species, I figured that was the easy way to eliminate additional tables. My original thought was in the form where it asks for Plant ID, the user could use an ID such as "ALL" and it would replicate that action for all plants in the greenhouse (i.e. All the plants get watered at the same time so I'd like to use 1 entry to mark all plants as watered (but I want it to show individually under each plant). If there is a bed specific task (i.e. fertilizing for a specific issue such as a nitrogen deficiency that would only apply to the 18 plants in that bed) I'd like to use an ID such as "BED 4" to show that all the plants in bed 4 were treated for a nitrogen deficiency under each plant individually but only for the plants contained in that bed. If there is a plant specific action just the plant ID can be entered.

I have made the edits suggested here and above, here is the updated file.

Greenhouse DB (2).zip

[mike60smart]

Hi
Nearly.
Your Relationships should be as shown in the screenshot attached.

PK in tblPlantList joined to FK in tblActionList

Also, your fields PlantInspection, Action Taken and Application Method, these should be lists in separate tables.

Sorry I now Attach the screenshot file

[June7]

Mike, no image attached to your post.

LXIX, regardless of data structure, technique for batch adding records still applies. Did you try?

[LXIX]

I have not yet, I have been in meetings all day.