Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 24th, 2007, 05:14 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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):

tblArticleDefinition:
ArticleID
ArticleDescription

tblArticleCostCenterDistribution
Article CostCenterDistributionID
CostCenter
CostCenterPercentage
ArticleID

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:

tblSales
SalesID
SalesDate
RevenueCenter

tblSalesArticle
ArticleID
ArticleDescription
QuantitySold
SalesID

tblSalesCostDistribution
CostCenter
CostCenterDistribution
ArticleID

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!!!

Thank you!

 
Old April 24th, 2007, 06:53 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

See the post in the Access forum. The problem is the data normalization.

mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting related tables to excel crisan Access 10 July 24th, 2012 04:23 PM
single submit to related tables forumuser BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 December 2nd, 2007 10:29 AM
Temporary Tables Challenge! PCNHSL PHP How-To 1 November 8th, 2007 09:42 AM
Synchronising Two Sets of Related Tables Odeh Naber Access 11 May 14th, 2007 06:58 AM
Insert record Into 2 related tables at once kalchev ASP.NET 2.0 Basics 2 May 9th, 2006 05:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.