Structure Access please

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
SampleDataStruct.accdb
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayneAuthor Commented:
Right now it lives in excel currently like this…..
excelStructure.xlsx
0
Jeffrey CoachmanMIS LiasonCommented:
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...

tblProducts
pID (Primary Key)
pName
pPrice
...

tblDistPipe
dpID (Primary Key)
dp_pID (Foreign Key)
dpName
...

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

tblTier
tID (Primary Key)
t_dpID (Foreign Key)
tName
...

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RayneAuthor Commented:
Hello Jeff,

I remember your hilarious  comments here :)
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27948556.html

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 …
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jeffrey CoachmanMIS LiasonCommented:
...LOL...
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

;-)

JeffCoachman
0
RayneAuthor Commented:
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 :)

Rayne
0
Jeffrey CoachmanMIS LiasonCommented:
Yes, research as much as you can on Many-To-many relationships...
;-)
0
RayneAuthor Commented:
Hello Jeff,

Here is the follow up if you are interested...
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28286514.html

Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.