Solved

follow up Relate 2

Posted on 2013-12-16
22
261 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

816 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

8 Experts available now in Live!

Get 1:1 Help Now