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!

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

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

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!

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

mmcdonal

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

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

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

mmcdonal

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

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

Thanks!