MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 308|Reply: 5

large database, structure and software q's [SOLVED]

[Copy link]

3

Topics

29

Posts

67

Integral

Member

Rank: 2

Integral
67
Post on 9-14-2016 18:32:23 | All posts |Read mode
Hi,
Im trying to build a new database out of what we've got at the moment we have a big database of 6000 retail stores divided into 4 divisions, 21 regions and 110 areas with each of them managed by a divisional, regional, and area managers. Each store holds a number of fields of information. all this is kept in one excel sheet, so 6000 rows down, therefore i'm not happy with it and trying to find ways of improving. im thinking splitting database in smaller parts and linking them together in access, however i am not sure what an efficient way of making changes to stores would be. i.e a regional managers wants to move some of his/her stores from one area manager to another,in excel that could be done using vlookup to filter relevant stores, but how can this be done in access? should i still keep an excel database for convenience of changing multiple stores? Im new to databases so any help is appreciated and sorry if my question is ambiguous, will give more details if needed. and would the best practice of moving, creating database be?
Thanks













Reply

Props Report

3

Topics

151

Posts

377

Integral

Conqueror

Rank: 3Rank: 3

Integral
377
Post on 9-14-2016 19:15:17 | All posts



Do you understand what database normalisation is? If not, start there, you can't come up with a design before you understand the fundamentals.
Some good intros to databases:
http://www.studytonight.com/dbms/dat...malization.php
http://forums.aspfree.com/microsoft-...es-208217.html
How you structure the database really depends on the business process. For example, you may start with the following 5 tables:
Stores
    Store Id
    Manager Id
    Area
Areas
    Area Id
    Manager Id
    Region
Regions
    Region Id
    Manager Id
    Division
Divisions
    Division Id
    Manager Id
Employees
    Employee Id
    Employee First Name
    Employee Second NameSo a store has a single area, which belongs to a region, which belongs to a division. Each of these has a manager.
Moving a store area simply means changing the Area in the storeDetail table, that will effectively change the division and region to whatever the new area is.
This may not be flexible enough for your requirements, but it could be a starting point




Reply Support Opposition

Props Report

3

Topics

29

Posts

67

Integral

Member

Rank: 2

Integral
67
 Author| Post on 9-14-2016 20:16:18 | All posts



Hi Kyle,
Thank you for your response. At this point is a bit too much to do, to move all the data from excel to access, so instead im trying to link tables together and you perhaps would know if its viable, as I'm strungling to make this happen.
i have two small excel databases that i want to link together via access, because when creating linking formulas in excel, they take too much time to process. So my idea is to create a linking table in access of one excel database and have it linked back to another excel database. in this way someone can have changes to the first database and its reflected in another, so you got the idea.
whats not working at the moment, i can't export linked excel table from access, but surely there's got to be a way of doing it. i tried researching bu went nowhere. also in general is this a good idea, should i even move forward with it, will it be efficient in terms of processing speed and updates?
Thanks
Reply Support Opposition

Props Report

11

Topics

130

Posts

283

Integral

Conqueror

Rank: 3Rank: 3

Integral
283
Post on 2-6-2017 20:50:59 | All posts



the simple formula












Reply Support Opposition

Props Report

13

Topics

1065

Posts

2355

Integral

King

Rank: 6Rank: 6

Integral
2355
Post on 2-6-2017 21:03:44 | All posts



What is the source of the #VALUE# error? My first thought would be to nest the current function in B so that it returns some kind of text string when it errors, then the sum function ImramBhatti suggested would work fine. Are you familiar with the IFERROR() function? https://support.office.com/en-us/art...6-63f3e417f611
Something like =IFERROR(current formula in B,error)











Reply Support Opposition

Props Report

14

Topics

436

Posts

1026

Integral

King

Rank: 6Rank: 6

Integral
1026
Post on 2-6-2017 21:23:58 | All posts



This is inappropriate language. Further offenses will result in a ban.

Common courtesy is the order of the day. Avoid coarse language, personal attacks and inflammatory remarks. Address your concerns at topics and not at people.
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-18-2017 05:01 , Processed in 0.260149 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!