Solved

follow up Relate 2

Posted on 2013-12-16
22
259 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
  • 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
 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now