Solved

follow up Relate 2

Posted on 2013-12-16
22
269 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 84
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

733 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