Structure Access  please

Posted on 2013-08-28
Medium Priority
Last Modified: 2013-11-05
Hello to All Access Gurus,

Need help organized data that is not organized into MS access.
Criteria –

A distributor pipeline (DP) is a way/medium to sell a product – to the government or private or public entity etc
Each product can have multiple DP. Like, a product P1 can be sold to DPcodes 001,002,006 and product P2 is only sold to DPcodes 007
Each DP can have a set number of Tiered price. For example: DPCode 001 has only 1 tiered price (tier1)
and DPCode 002,006  have three tiered pricing (tier1,tier2,tier3)
and 006 has 2 tiered price (tier1,tier2)

Organizing this into multiple tables in access – I think I may be incorrect – Please suggest the best way to get this organized in multiple tables -

Assume there are two tables –
[tblProduct] that stores productCode as primary key –total  fields  are – {prodCode,DPCode}
[tblDP] that stores DPCode as primary key – total field are – {DPCode,Tier1,tier2….tierN}

Thank you
Respect, Rayne
Question by:Rayne
  • 4
  • 3

Author Comment

ID: 39446941
Right now it lives in excel currently like this…..
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 39447431
Remember we have do not know the details of your data, no do we know the business rules governing this system.

From your experience here you know that the Experts here cannot really do specific "design" work.
Database design requires an intimate knowledge of all the aspects of the project, and a lot of back and forth is involved between all those involved.
We can only make suggestions based on general database theory.

It is also not known what your skill level is with table design or table relationships.

This all being said, this is how I see it...

pID (Primary Key)

dpID (Primary Key)
dp_pID (Foreign Key)

tblDistPipeTier (Many-to-many table, between tblDistPipe and tblTier)
dptID (Primary Key)
dpt_dpID (Foreign key)
dpt_tID (Foreign Key)

tID (Primary Key)
t_dpID (Foreign Key)


Author Comment

ID: 39447771
Hello Jeff,

I remember your hilarious  comments here :)

I apologize for asking this question. I haven't  touched access data structure designing in years. so ended up asking this. But thank you ;) for your kind help and effort in this regards. I see your solution as a great head-start to my problem and a way to initiate the data refining and designing.

Thank you Again for coming forward and helping,
Respect …
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39447787
I do remember that post...

No need to apologize buddy, it's a perfectly valid question...

I based my design on what I felt you were asking for...

As with all design questions, you should not take what I posted here as the actual design to follow.
Create a test database with it, and test it against how your business rules apply. Then post any feedback



Author Comment

ID: 39447800
Hello Jeff ;)

Sure, as you indicated earlier - I will further do a lot of research to refine the business requirements and start creating a DB using your solution as a idea plus what I find out more from my side. And then trying to test create something that fits the final logic. But yes, feedback will come in :)

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39447814
Yes, research as much as you can on Many-To-many relationships...

Author Comment

ID: 39626246
Hello Jeff,

Here is the follow up if you are interested...

Thank you

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
This tutorial summarizes the causes behind"an unknown error prevented access to the PST File”.  It also suggests the various solutions to fix the problem.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

624 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