?
Solved

follow up Relate 2

Posted on 2013-12-16
22
Medium Priority
?
279 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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