Please excuse my stupidity but I am going round in circles with a problem and I'm sure its something really simple I'm missing.
I have a Form that stores details of an item I make and also contains a couple of fields that store its status eg Sold, For Sale etc. The other field records the event it was sold at. This is in the form of a drop down list that gets its values from another form that stores information about the event including its name which is unique. If an item is not sold, the field will be empty.
What I want to do is to have a relational field on the events form (essentially a sub-form) that is populated by the items sold at that event so I can calculate profit/Loss info for the event based on sales. (Its usually a big fat Loss!!!).
The database is already heavily populated with records so manual entry/updating isn't an option i really want to consider unless there are no alternatives. Ive tried adding a relational form to one or both forms, Ive tried relational look-ups, Ive tried everything I can think of in fact but although sometimes the preview shows records the sub-form/relationship field remains blank.
Ive managed to come a long way in my database skills with help on this forum but some things just seem to be beyond my grasp.
Can anyone please put me out of my misery? You will need to explain in simple terms please!
Yes, you will need a Many 2 Many relation field for starters. This can be used to calculate the sum of all sales.
I order to automatically populate the many 2 many filed, you can do that by populating it based on a recordset variable.
How you set the recordset variable is pretty straight forward. You run a query that finds all records that contain the word “Sold” in whatever specific field your using to store that value. A recordset can be populated from the results of a query.