Solved

Follow up Relate

Posted on 2013-12-05
7
327 Views
Last Modified: 2013-12-16
Hello

This is is reference to the previous question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28286514.html
Each item can belong to several distribution pipelines (item 11234 can be sold to grocery, government)
Each distribution pipeline can have several items (in grocery pipeline, there are  10000 items sold)


Each distribution pipeline can have several tiers (grocery pipeline has 4 tiered cost – tier1,tier2,tier3,tier4 while government pipeline only has 2 tiered cost ( tier1,tier2)


Each tier can be contained in several distribution pipelines (grocery pipeline and government, both have tier1 bracket for items)


I kept LCMConsulting’s advice and then further study my data. I came with the relationship in the attached access file.
The new thing – an item can be sold to many countries and each country can have several items – so it’s a many to many – hence I created a connecting table between item and country – itemCountryline and now….
I tried joining in itemcountryline to another table DistPipeTier…Can you please check if my making of relationship makes any sense?

Thank you
sampleSome.accdb
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 

Author Comment

by:Rayne
ID: 39699356
All Experts are welcomed :)
Is my relationship design makes sense?
Efficient enough for creating good forms and filling up the tables?
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 39699681
First I would use an autonumber (system assigned value)  as the primary key for every table. I believe that was also LCMConsulting’s advice advice. Why was the reason for not following that?

I would use the system assigned (auto number) field for all relationships. I would would avoid primary keys that use multiple fields. From what I have seen there is no reason to use multi-field keys.

I do not worry about forms and filling up the tables at this point. I would be more concerned with creating the required reports.  It would really help to know what reports you will need to pull from the data. Put another way, what are you wanting to know from the data?

What information you need out is a good indication of what needs to go in.
--Boyd Trimmelll , aka HiTechCoach

It would also help to see more sample data in each table to get a better idea of the data that must be stored.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39700598
I agree with HTC - there's no real reason to use 3 fields as the PrimaryKeys in those two tables. Use the AutoNumber instead.

You can leave the 4 Fields as a Unique Index, to stop duplicated data from being inserted - just make tblDisPipe_Tier.Key and tblItemCountryLine.keyline your PKs, and use those when relating tables.

Now whether this is the correct design or not remains to be seen. We don't know a tremendous amount about your project, or the data it stores and the way that data is related in the real world, so it's kinda hard to make specific suggestions.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Rayne
ID: 39701864
Thank you both so much for your priceless advices. I will come back with more questions and data :)
0
 

Author Comment

by:Rayne
ID: 39722026
Hello All,
I am back with more info….
An item can belong to several countries and each country can have several items –
There is price categories per item- an item can belong to several different price level for the SAME distribution pipeline

Refer to previous post for more relationship info….

Now the easy part is that - the need is for ONLY ONE report out of this database – which lays all the different info set in one line like the attached xlsx file.

So how can I make the DB scalable for storing new data (also and keeping in mind the relationships) and so that the data is stored in the most efficient way, but when I need the report out of it – I can get that all in one row
Please assist
Thank you
sampleSome2.accdb
data-look-this.xlsx
0
 

Author Comment

by:Rayne
ID: 39722046
0
 

Author Comment

by:Rayne
ID: 39722051
We can scrap some of the tables if that doesnt make any sense - I just want to make sure that the relationship is somehow reflected...for data storage and scaliblity
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question