I Challenge U! Sync of Two Sets of Related Tables
Hiya folks! Heres a challenge for you! Cant seem to figure it out myself, but sure it can be done! Hope you can help! I will first define what I am trying to do... and then mention the problem I am having.
I have this Access database.... and I have 5 tables in it.... the first two are related tables (using the ArticleID field):
In the above two tables, I define the food products that are sold in a hotel. In the first table, I give the product a name, and in the second table, I define in which section of the hotel the food article is produced. For example, a "food platter" can be product "50%" in the "main hot kitchen" and "50%" in the "pastry section". A "Coca Cola" can be produced "100%" in the "Bar". So these two are purely for defining the articles.
The next three tables are related to one another and are for the user to register the daily sales. They are not related in any way to the first two tables that I mentioned above:
The first two tables in this set are related using the SalesID field, and the last two in this set are related using the ArticleID field.
I created a form, with a subform and a subsubform using these three related tables. The user would first register the date that the sales took place and the location in the hotel where the sales were made (i.e. bar, restaurante, room service, etc.). In the subform, he would register the products that were sold on that date and section and the quantities sold. The subsubform should fill in automatically, based on the information defined in the first set of tables mentioned earlier. This is what I cant seem to do.
For example, if the user selected a "Coca Cola" in the subform, the subsubform should automatically fill in "Bar" and "100%". If the user selects "Food Platter" in the subform, then the subsubform should automatically fill in "Main Hot Kitchen" and "50%" AND "Pastry Section" and "50%". These were already defined in the first set of two tables mentioned earlier in this post.
Can anyone help? Trying to figure out the solution is driving me crazy! I would appreciate it!!!