Duplicating record occurence in a query (1 Viewer)

[fearoffours]

Hi there, I feel like this could go in one of several fora here, but think that this one suits best!

I have a database which is essentially a plant catalogue for a garden centre.
One of the functions of the database is to print labels for pots with some detail about the plant, price, care instructions etc.

Individual plant records are held in the tblPlants table. This features a Yes/No field titled 'Print'

Currently I have a form frmPrint which assembles a print queue for the labels. This form has 2 continuous subforms. The first sbfrmPlantSearch lists the results from a search of the database for the plant name. This features a checkbox bound to the aforementioned Print field.
The second subform sbfrmPlantPrint, is bound to a query with the criteria Print=Yes.
Ticking the checkbox next to a record on sbfrmPlantSearch therefore adds that record to sbfrmPlantPrint. The user can amend the price and label quantitiy at this point on the sbfrmPlantSearch. These are then the records that are printed onto the labels.

All this so far works as planned. However we have encountered a situation whereby our plant buyer has now ordered several specimens of the same plant but with different habits and different prices. It is obviously poor relational design for the suer to enter multiple entries in tblPlants for the same type of plant. The user could assemble separate print queues for each type of this plant, but this also seems like a waste of the users time.

The ideal solution as I see it is for there to be a way of adding the plant record to sbfrmPlantPrint multiple times. At this point the user can amend through a combo box the type of habit the plant displays before executing the print command.

My current solution is to remove the Print field from tblPlants. I will instead add a button to the frmPrint which will copy the selected record to a new table - tblPrint. This new table will then include the new 'Habit' field, and sbfrmPlantPrint will be based on a query of this table. (As will the label report). sbfrmPlantPrint will also include an editable combo box for the habit field. There will then have to be a 'clear print list' button on frmPrint which will delete all records from tblPrint.

Is this the best solution? (Having had to write it out carefully in order to explain, I am even more convinced this is the right way to solve this problem!)

 

[neileg]

All this so far works as planned. However we have encountered a situation whereby our plant buyer has now ordered several specimens of the same plant but with different habits and different prices. It is obviously poor relational design for the suer to enter multiple entries in tblPlants for the same type of plant. The user could assemble separate print queues for each type of this plant, but this also seems like a waste of the users time.

You have to consider this carefully. Although the plants may be the same species (or whatever the term is) the fact that they have different habbits makes them a different item. To function as a proper catalogue, it seems right to list these separately, even if you want to group them. So an XYZ rose as a standard is not the same as an XYZ rose as a bush.

 

[fearoffours]

You have to consider this carefully. Although the plants may be the same species (or whatever the term is) the fact that they have different habits makes them a different item.

Although I can see where you're coming from, to have multiple entries in the catalogue for what is effectively the same plant with the same care details (eg the many different forms of clipped box) seems to be an unnecessary replication of data within the database. I'm not sure if your suggestion is contrary to all the advice of normalisation?

 

[neileg]

You can't change the real world to suit your database! If the care details are the same for multiple entries, then proper normalisation will lead to care details being held in a separate table with a one to many relationship with the plant table. I can envisage the same care details relating to plant of different species, too, so this will improve your level of normalisation, not decrease it.

 

[fearoffours]

Hmm. OK, this does indeed require some more thought then.
I can't see my end users liking it!

 

[Rabbie]

You can't change the real world to suit your database!

So that's where I have been going wrong. I must have missed something when I fixed a problem by tweaking reality to fit the database

 

[neileg]

Hmm. OK, this does indeed require some more thought then.
I can't see my end users liking it!

I would have thought it would be invisible to the end users. The structure of your tables should be driven by efficient design. The users see forms and reports, not tables.

 

[neileg]

So that's where I have been going wrong. I must have missed something when I fixed a problem by tweaking reality to fit the database

Of course experienced database developers change reality. We just don't let on to novices!

 

[fearoffours]

I would have thought it would be invisible to the end users. The structure of your tables should be driven by efficient design. The users see forms and reports, not tables.

Indeed.
And having thought about it, I've realised that I have actually already done what you suggested wrt care instructions. The recommended compost, most suitable position, watering instructions, and best soil type are all held in individual external tables.
However tblPlants is a table cataloguing plant SPECIES, not individual plants. This is why I still believe that there should not be multiple entries in that table when the only thing different between records is the form of that plant.

 

[neileg]

I think you're approaching this as a horticulturalist, not a database developer! My advice is unchanged, but it's your database

 

[fearoffours]

I think you're approaching this as a horticulturalist, not a database developer!

Maybe! Looks like I'll have to live with that!
I have actually gone ahead and implemented the solution I suggested in my OP, and am very happy with the way i's working. If a database professional ever takes over, I'll have to live with his wrath!