How to organise data where it has 1 to many relationships

I have data that are 1 to many relationship.
For eg, The book with the same title can be in multiple locations and i would like to find out the information related to the user borrowing in multiple locations - both the user information + the respective multiple locations information.

How do i organise the data in excel so that i can still analyse and also able to extract data easily ?

Prob now is for eg. i have a row but also multiple row that is pertaining to the same book title so it would be very misleading to count the number of rows to get the total number of books borrowed since some rows are like "repeated" and not the actual unique data.
bluue sAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David Johnson, CD, MVPOwnerCommented:
IMHO you are using the wrong solution for this problem. A database would be better
you  have a book say "My book" , you have several locations (loc1,loc2, loc3, loc4
You have a total of 15 copies of the book.
loc1 has 5 available
loc2 has 1 available
loc3 has 0 available
loc4 has 0 available
sum location(s) available and subtract the result from total available to get # of available books
15660
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
I would agree with David, you are trying to use a screwdriver, when you need a wrench.  Use Access for this type of thing, not Excel.

Dale
0
bluue sAuthor Commented:
ok so meaning if i want to have pivot charts like in excel withe trend lines etc, i would need to link the access db to excel ?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeCommented:
What you are talking about are relational database concepts, you have many books, and many locations, and any book may or may not be located in any location, so in Access you would need a Books table, a Locations table and a BookLocation table to track what location each book is in.

Access has the ability to create reports and queries which would help you visualize where each book is located, in a grid, if that is what you are looking for.  Or you could create an Access query and push or pull the results of that query into Excel.
0
bluue sAuthor Commented:
Has anyone tried this https://www.opengatesw.net/index.htm 

I am just thinking if anyone has tried this software and how's the review like from a user point of view (genuine not those marketing gimmicks). Thanks.
0
Rob HensonFinance AnalystCommented:
pivot charts like in excel withe trend lines etc

How are you planning to chart the location of books?

For example, you could have a chart with Book Title along x axis and quantity up the y axis. Each location would then have its own entry for each book. This would look ridiculous if you're talking about hundreds of book titles.

With Pivot Chart I guess you could filter on Book Title so that you're only seeing data for one book and a column for each location.

Why do you want trend lines?
0
bluue sAuthor Commented:
@Dale: Ok, meaning if i need to know who are the profile (demographics) of the users who borrow the books, usually which location is most popular, what books are popular with the people, when is the time where people borrow the most books , I can push the data from Access to Excel to run pie charts, trend lines etc ? I need visualization of data, not use the data only.

Meaning one needs to have a certain level of Access knowledge to run the query. Is this correct ?
0
Dale FyeCommented:
Bluue s,

Yes, one needs to have a certain level of Access knowledge.

Where, how is this information being stored now?  If it is being stored in Excel, you obviously have some really large excel spreadsheets with lots of redundant data.
0
bluue sAuthor Commented:
I will explore MS Access.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.