Start Free Trial

asked on

follow up Relate 2

Hello All,

in reference to this previous question-
https://www.experts-exchange.com/questions/28311549/Follow-up-Relate.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

ASKER

All Experts are welcome :)

ASKER

I am getting errors when I am triyng to jion Tier with Dispipetier...check attached
errorLog.docx
sampleSome2.accdb

ASKER

I did changed keys as per previous suggestions....

ASKER

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

Scott McDaniel (EE MVE )

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

ASKER

Awesome LSMConsulting. I am looking.

Thank you for your help.

ASKER

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

ASKER

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

Scott McDaniel (EE MVE )

Can you post your relationships diagram now that you've made the changes?

ASKER

Hello LSMConsulting,

Here it is
sampleSomenew.accdb

ASKER

added itemIT in the countryline table as per your post

https://www.experts-exchange.com/questions/28319396/follow-up-Relate-2.html?anchorAnswerId=39722802#a39722802

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

ASKER

i am noobie so learning all the way

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you LSMConsulting for your brilliant help. I cant thank you enough for your kindness. I am looking

ASKER

I will follow up :)

ASKER

Hello LSMConsulting
Follow up posted here:
https://www.experts-exchange.com/questions/28320739/Follow-up-3.html

Thank you again

ASKER

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

https://www.experts-exchange.com/questions/28320739/Follow-up-3.html?anchorAnswerId=39725879#a39725879
howDoesThisLook.accdb

ASKER

And let me if this will be enough for producing the one and only report as desired format

Thank you

ASKER

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

Scott McDaniel (EE MVE )

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.

ASKER

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

ASKER

Hello,

Can you assist wit this ?
https://www.experts-exchange.com/questions/28336775/Access-Form-This.html

Thank you