Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access 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 23rd, 2007, 03:58 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default Synchronising Two Sets of Related Tables

Hiya folks! This is kinda different than the usual synchronising combo box issue.... 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? I would appreciate it!!!

Thank you!

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

No it shouldn't do that. That information is stored in the tblArticleCostCenterDistribution table. You can reproduce it in reports, but there is no need to display this on a form, especially if the user is not going to modify it there. That is an attribute of the Article. In fact, this is the table structure I would use:

tblArticle
ArticleID - PK
ArticleDescription - text
ArticlePrice - currency (no cascading updates on referential integrity)

tblCostCenter
CostCenterID - PK
CostCenterName - text

tblArticle_CostCenter
Article_CostCenterID - PK
ArticleID - FK
CostCenterID - FK
PerCent - number

Then on the main form for tblArticle, use the junction table as a subform, and allow the adminsitrator to set the percentages there.

Then on you sales form, the user only needs to see the article, not the percentages and break down by cost center. If you need to do something with that data, like distribute sales, then you can do that in a seperate function, not on the subform.

tblRevenueCenter
RevenueCenterID - PK
RevenueCenterDesc - text
etc

tblSales_Article
Sales_ArticleID - PK
SalesDate - date/time
RevenueCenterID - FK
ArticleID - FK
QuantitySold

Did that help any?




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

Actually, I am not sure of the best place for the Article Cost column. That should be more static. If you were packaging queries and outputtng them and not storing the results, then it wouldn't matter if you raised the price.

mmcdonal
 
Old April 25th, 2007, 08:32 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi Mmcdonal!

Thanks for your help on this one! I did what you said... and it looks great so far (the only thing I changed was in your tblArticle (i created an "ArticleCost" field instead of an "ArticlePrice" field)... but there is just one little issue that I cant seem to overcome.

Let us assume that I sold the following:

1 x Coca Cola with cost of 2 euros (cost center 100% bar)
2 x Hamburger with cost of 4 euros (cost center 100% hot kitchen)
1 x Food Platters with cost of 10 euros (cost center 80% hot kitchen and 20% pastry)

The report I want to generate from this is the following:

BAR
Coca Cola, 2 euros, 100%, 1, 2 euros
Total Cost from this Cost Center = 2 euros

HOT KITCHEN
Hamburger, 4 euros, 100%, 2, 8 euros
Food Platter, 10 euros, 80%, 1, 5 euros
Total Cost from this Cost Center = 13 euros

PASTRY
Food Platter, 10 euros, 20%, 1, 5 euros
Total Cost from this Cost Center = 5 euros

Maybe this guide will help u to understand what each of the numbers mean in the report:

BAR (cost center)
Coca Cola (sold article), 2 euros (total cost), 100% (% of total cost coming from this cost center), 1 (total quantity sold), 2 euros (value of cost that comes from this cost center)
Total Cost from this Cost Center = 2 euros

I have been able to get all of this into the report, except for the "Quantity Sold" field. Not sure what is going on.

I appreciate your help! Thanks!
 
Old April 25th, 2007, 12:13 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

What is the SQL statement from the query behind your report? Can you post that?

mmcdonal
 
Old April 26th, 2007, 02:16 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi again! Here is the query that I built. If I keep the QuantitySold field (from tblSales_Article) in the query, the report shows up completely empty. If I remove the QuantitySold field from the query, the report shows up, but it wont display the quantity sold, as I need it to. My best guess is that something is wrong with the way I set up the relationships... (but since I am not a pro at Access programming, I could be wrong!)

SELECT tblCostCenter.CostCenterName, tblArticle.ArticleDescription, tblArticle.ArticlePrice, tblArticle_CostCenter.Percent, tblCostCenter.CostCenterID, tblSales_Article.SalesDate, tblSales_Article.QuantitySold
FROM tblRevenueCenter INNER JOIN ((tblArticle INNER JOIN (tblCostCenter INNER JOIN tblArticle_CostCenter ON tblCostCenter.CostCenterID = tblArticle_CostCenter.CostCenterID) ON tblArticle.ArticleID = tblArticle_CostCenter.ArticleID) INNER JOIN tblSales_Article ON tblArticle.ArticleID = tblSales_Article.ArticleID) ON tblRevenueCenter.RevenueCenterID = tblSales_Article.RevenueCenterID;

Thanks!!!

 
Old April 26th, 2007, 02:29 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

These are the tables I created with their relationships, in case you need them... wasnt sure how to attach it to this post. I only changed the location of the "SalesDate" field:

TBLSALESDATE
SalesDateID (PK)
SalesDate

TBLREVENUECENTER
RevenueCenterID (PK)
RevenueCenterDescription
SalesDateID (linked to tblsalesdate using SalesDateID - one-to-many - enforce ref. integrity on update and delete.

TBLSALES_ARTICLE
SalesArticleID (PK)
RevenueCenterID (linked to tblRevenueCenter using RevenueCenterID - without enforcing ref. integrity)
ArticleID (linked to tblArticle using ArticleID - without enforcing ref. integrity)
ArticleDescription
QuantitySold

TBLARTICLE
ArticleID (PK)
ArticleDescription
ArticlePrice

TBLARTICLE_COSTCENTER
Article_CostCenterID (PK)
ArticleID (linked to tblArticle using ArticleID - without enforcing ref. integrity)
CostCenterID (linked to tblCostCenter using CostCenterID - without enforcing ref. integrity)
Percent

tblCostCenter
CostCenterID (PK)
CostCenterName

Thanks again!!!


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

You don't have to display this in the query, just aggregate it in the report. Did that work?

mmcdonal
 
Old April 26th, 2007, 10:08 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Sorry but what do you mean by "aggregate it in the report"? Thanks!

 
Old May 3rd, 2007, 06:01 AM
Authorized User
 
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Hi Again! Sorry but wasnt sure what you meant by "aggregating".

Thanks!






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
I Challenge U! Sync of Two Sets of Related Tables Odeh Naber Access VBA 1 April 24th, 2007 06:53 AM
Insert record Into 2 related tables at once kalchev ASP.NET 2.0 Basics 2 May 9th, 2006 05:10 AM
Insert on 2 Related tables question acdsky Classic ASP Databases 1 July 21st, 2004 09:29 PM





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