Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

follow up Relate 2

Posted on 2013-12-16
22
Medium Priority
?
285 Views
Last Modified: 2014-01-12
Hello All,

in reference to this previous question-
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28311549.html



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
data-look-this.xlsx
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
  • 18
  • 4
22 Comments
 

Author Comment

by:Rayne
ID: 39722058
All Experts are welcome :)
0
 

Author Comment

by:Rayne
ID: 39722341
I am getting errors when I am triyng to jion Tier with Dispipetier...check attached
errorLog.docx
sampleSome2.accdb
0
 

Author Comment

by:Rayne
ID: 39722355
I did changed keys as per previous suggestions....
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Rayne
ID: 39722393
I tried to do what LSMConsulting said
"make tblDisPipe_Tier.Key and tblItemCountryLine.keyline your PKs, and use those when relating tables."
Not sure how that could be done...please assist
sampleSomenew.accdb
0
 
LVL 85
ID: 39722802
You must use the Primary Key of a table when relating it to another table.

For example, if you want to relate tblItem (the "parent") with tblItemCountryLine (the "child") , you would do this:

1. Add a field to tblItemCountryLine to store the related value - for example: ItemID
2. Relate tblItem.keyi to tblItemCountryLine.ItemID

See the image attached - note the new column I added, and new relationship line between those two tables.
RElationships.png
0
 

Author Comment

by:Rayne
ID: 39723630
Awesome LSMConsulting. I am looking.

Thank you for your help.
0
 

Author Comment

by:Rayne
ID: 39723661
Hello LSMConsulting,
Ok, I revised what is needed - - repeated lines of item needs to be generated in such a way so that there is a macro laden xlsm file (don’t worry aobut the macro file - it’s a simple excel with checkboxes)  that will read the raw data (users don’t have MS Access their pc) .
The xlsm file will have check boxes for users so that users could filter on country numbers and the view the data within that. They can also filter on the distribution pipeline checkbox and filter on items that are specific to the that distribution pipeline.
But the actual data storage will be in Access. Its only when  the access spits out the extract - it's in this attached format for filtering capabilities.
Rayne
data-look-this22.xlsx
0
 

Author Comment

by:Rayne
ID: 39723673
do you think the current structure of Access relationship will help me to get to my final goal report? or there are any other intermediate steps....feel free to suggest.

Thank you
0
 
LVL 85
ID: 39723969
Can you post your relationships diagram now that you've made the changes?
0
 

Author Comment

by:Rayne
ID: 39724079
Hello LSMConsulting,

Here it is
sampleSomenew.accdb
0
 

Author Comment

by:Rayne
ID: 39724089
added itemIT in the countryline table as per your post

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28319396.html#a39722802

- not sure how that will be filled as data is filled in all of them...anyways..
0
 

Author Comment

by:Rayne
ID: 39724092
i am noobie so learning all the way
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39725344
Your relationships still aren't setup correctly, except for tblItem - tblItemCountryLine.

You must store the PRIMARY KEY of the Parent table in the Child table - for example, in tblDistPipe_Tier, if you need to relate tblDistPipeline, you would store the value of tblDistPipeline.keyDP in tblDistPipe_Tier (right now you're storing the value from tblDistPipe_Tier.dptCode into tblDistPipeline.dbCode, which is backwards).

Once you get the relationships correct, you'll be able to create a query that will output the data as needed to Excel.
0
 

Author Comment

by:Rayne
ID: 39725358
Thank you LSMConsulting for your brilliant help. I cant thank you enough for your kindness. I am looking
0
 

Author Comment

by:Rayne
ID: 39725361
I will follow up :)
0
 

Author Comment

by:Rayne
ID: 39725445
Hello LSMConsulting
Follow up posted here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28320739.html

Thank you again
0
 

Author Comment

by:Rayne
ID: 39725881
Hello LSMConsulting,

I did posted the final file with the changes - I am getting near to understnaidng how all the keys work. Please take a look when possible

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28320739.html#a39725879
howDoesThisLook.accdb
0
 

Author Comment

by:Rayne
ID: 39725890
And let me if this will be enough for producing the one and only report as desired format

Thank you
0
 

Author Comment

by:Rayne
ID: 39725892
Also  - do you think i left a lot of extra fields in some table ? not sure so just checking if I accidentally made it redundant
0
 
LVL 85
ID: 39726258
I can't really tell you whether a field belongs in a table. Generally speaking, each field in a table should be an "attribute" of the entity - for example, a table named Car would have fields for Make, Model, Engine, etc, but it would not have a field for Driver.
0
 

Author Comment

by:Rayne
ID: 39726663
Sure, ok. So given from last attached file, is it possible to generate the target extract?  I did comnected the autonumber pks...please update
0
 

Author Comment

by:Rayne
ID: 39775768
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

610 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