For the sake of ease and avoid building access knowledge from scratch, decided to build my data in related tables in excel. Just to make sure you know what I mean; client master data table(s) each has a customer number to relate these tables, this applies to product tables, orders tables use both customer numbers and product numbers to relate these records to the data in customer and product tables. A relational database using several worksheet. Of coarse I can provide a sample, but this is a design stage and I need a general answer. Can this be done in excel or I have to go the "access" route.
Emulating relational Database in excel
- Thread starter arishy
- Start date
NARAYANK991
Excel Ninja
Hi ,
It depends on a couple of factors.
Do you wish to take out reports , or do you want to implement some kind of a dashboard to present this data in different ways ? Report generation is much better than in Excel ; dashboards are much easier in Excel than in Access , at least in my opinion.
If you don't intend to have others use whatever application you are developing , then you are better off using Excel , since you can do things faster in Excel than in Access.
If you wish to develop a professional application which will be used by others who do not know much about databases , then you are probably better off doing it in Access , since you can ensure that the data and the application are fully protected from the users.
A last point is whether this will be a one-off exercise or you will be doing this sort of work regularly ; if it is a one-off exercise , then building up your knowledge of Access from scratch may not be of much benefit ; if you plan to do this kind of work regularly , then building up your knowledge of Access is not only good for the job , but may benefit you too in the long run.
Narayan
It depends on a couple of factors.
Do you wish to take out reports , or do you want to implement some kind of a dashboard to present this data in different ways ? Report generation is much better than in Excel ; dashboards are much easier in Excel than in Access , at least in my opinion.
If you don't intend to have others use whatever application you are developing , then you are better off using Excel , since you can do things faster in Excel than in Access.
If you wish to develop a professional application which will be used by others who do not know much about databases , then you are probably better off doing it in Access , since you can ensure that the data and the application are fully protected from the users.
A last point is whether this will be a one-off exercise or you will be doing this sort of work regularly ; if it is a one-off exercise , then building up your knowledge of Access from scratch may not be of much benefit ; if you plan to do this kind of work regularly , then building up your knowledge of Access is not only good for the job , but may benefit you too in the long run.
Narayan
A well rounded answer...thanks.
If I understood you correctly, you did not "brush" completely the idea that it can be done in excel. Until I get "my house in order; by starting the access route"; can you just help me with this:
If I have two tables with "related" data say customer number(key); how can I create a third table that has cells from both tables for each customer number.
Say table one: key,aaa
table two: key,bbb
Table three:key,aaa,bbb
VBA is a viable solution, if others fail
If I understood you correctly, you did not "brush" completely the idea that it can be done in excel. Until I get "my house in order; by starting the access route"; can you just help me with this:
If I have two tables with "related" data say customer number(key); how can I create a third table that has cells from both tables for each customer number.
Say table one: key,aaa
table two: key,bbb
Table three:key,aaa,bbb
VBA is a viable solution, if others fail
NARAYANK991
Excel Ninja
Hi ,
In general , if there is a one-to-one relationship between the data in a table , then given the key field , retrieving all other data from that table is a matter of using the VLOOKUP function or a combination of the INDEX / MATCH functions.
If there is a one-to-many relationship between the data in a table , as in a customer -order table where one customer can have many orders , retrieving all orders for a particular customer can be done by using a combination of the INDEX / SMALL / IF functions.
Narayan
In general , if there is a one-to-one relationship between the data in a table , then given the key field , retrieving all other data from that table is a matter of using the VLOOKUP function or a combination of the INDEX / MATCH functions.
If there is a one-to-many relationship between the data in a table , as in a customer -order table where one customer can have many orders , retrieving all orders for a particular customer can be done by using a combination of the INDEX / SMALL / IF functions.
Narayan
