Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2011
    Posts
    28

    Unanswered: Simple Database - Rly Struggling :(

    I just cant get my head around how to build a simple database the way I want it (in Access)!! But its soo damn hard!!!

    Its mainly the structure that proves to be a real brain mash.... what is the best way to start out getting better at access? I know what i want to do i just cant get into the Access way of thinking... i'm a long term Excel hardcore user!!!!!
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I played with the example database northtraders started in access 2 and had no internet so just reading and try and try and try by my else

    excel hat on
    colums A-Z as the feildname
    rows as the Data


    think of
    Tables as the BONES of the system
    Querys as differnace views of the Bones
    Forms as the skin
    reports as removable skin
    rest later on

    so a Table (my rules)

    NEVER HAVE SPACES IN A FEILD NAME

    Customer - tablename
    CustomerID - PK primary KEY autonumber I dont use it only for link to other tables
    Customer - Customer Name
    Phone
    Fax
    Adate - date customer was added auto today date
    Address1
    ...
    ...
    ...


    INVOICE
    InvoiceID
    InvoiceNU the invoice number
    CustomerID
    .....
    .....

    InvoiceDetail
    InvoiceDetailID
    InvoiceID
    ProductID
    Price
    ...
    ...
    Payment
    PaymentID
    InvoiceID
    CustomerID
    Pdate paid date
    Amount
    ...
    ...

    by look at a table then looking at the Feildname you can see what other tables are link related to it

    eg ProductID tell me its in the PK key of the Product table


    QUERY

    in Execl =a1+b1

    in query you would just write

    a+b just just drop the rows the Query will do that for you A = feildname , B =Feildname

    if there 1 records it does it 1 time
    if there 10000000 its does it that many time
    Last edited by myle; 11-25-11 at 04:03.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Mar 2011
    Posts
    28
    Thanks for taking the time to make that reply Myle. Really puts things into perspective... I guess with Excel I've learnt how to break down formula's very easily and then feel really comfortable combining lots of diff complesx formula's but the bit i struggle with when it comes to access is the initial structure and relationships.

    I will take onboard everything you've said though!!!! The current Database that I am working on is like a resource plan for my department that I do admin for.

    So there's 4 teams... each team will do several tasks... and varying employees within each team will have the skills to do some if not all of these tasks. Some of these tasks can be shared amongst the teams, some cant. And then the other aspect is looking at the available hours in each team compared to the task demand in each team.


    Im at the stage at the moment where the hardest thing is piecing all of the above together. Like do I need 4 different tables for each team? Or would it be better in Access to just have one Table for the department? things like that - which are soo flexibile in Excel really kick me in balls when it comes to Access!!!
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  4. #4
    Join Date
    Nov 2011
    Posts
    413

    Get yourself a plan

    Quote Originally Posted by Range View Post
    I just cant get my head around how to build a simple database the way I want it (in Access)!! But its soo damn hard!!!

    Its mainly the structure that proves to be a real brain mash.... what is the best way to start out getting better at access? I know what i want to do i just cant get into the Access way of thinking... i'm a long term Excel hardcore user!!!!!
    Set down and write down what you want to do like make a way to track customers.Then create a blank database,create a new table and start inputting your fields where your data will be stored. (Example)
    CustID Set as AutoNumber and format as text as Primary Key,then add a filed named Customers,use text as format,then address,city,state,zip and so on and save.Then create a query and add the customers table to it and add all the fields to the query,do any sorting you want and save.Then create a form using the query as the data source.Once you are satisfied with your table,query and form,then create a report using the same query as the form and customize all as you want.Hope this helps.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I do is draw a box and put feild excel columns name into the box

    that box comes the table

    and so on where you have the same value in a box that come the link back to the other box.

    so cross out those and put the other BOXID in there

    there you have created the relationship Database
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Mar 2011
    Posts
    28
    Thanks for the advice guys, so I am going to plan the DB I'm currently working on in this thread and post my thought process (based on your advice); to essentially evolve my thinking so its fit for Access. The barrier that I've experience whilst trying to learn Access is that I get stuck and I cant rail-road through the problem like I can with Excel. Subsequently, reverting back to what I know best - Excel - so this time round my objective is to ensure I work through any difficult patches with good planning, which is where I seem to have failed previously.
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  7. #7
    Join Date
    Mar 2011
    Posts
    28
    Okay so I've had a little planning session in Excel (lols).

    Initially I thought I'd start with Tables as they seem to be the most important element when it comes to DBs and then upon approval I'll progress from there and revise based on any suggestions. Previously, I think I would have tried to spilt each team out individually but now I realise Access can handle just having 1 table as long as the links are correct.

    I'm thinking that the TeamID will be the main link between all 3 tables...? (DefaultTeam for TaskMatrix). And where I've put lookup next to the field I am hoping that team names/Tasks can just be lookedup from the tables to save on manually updating this when teams/tasks get added or removed. Appreciate any thoughts/suggestions/error highlighting...? Thanks
    Attached Thumbnails Attached Thumbnails Untitled.png  
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  8. #8
    Join Date
    Nov 2011
    Posts
    413

    Common ID to link tables

    Quote Originally Posted by Range View Post
    Okay so I've had a little planning session in Excel (lols).

    Initially I thought I'd start with Tables as they seem to be the most important element when it comes to DBs and then upon approval I'll progress from there and revise based on any suggestions. Previously, I think I would have tried to spilt each team out individually but now I realise Access can handle just having 1 table as long as the links are correct.

    I'm thinking that the TeamID will be the main link between all 3 tables...? (DefaultTeam for TaskMatrix). And where I've put lookup next to the field I am hoping that team names/Tasks can just be lookedup from the tables to save on manually updating this when teams/tasks get added or removed. Appreciate any thoughts/suggestions/error highlighting...? Thanks

    You will need a common ID (Like TaskID in your first table) to link them.

  9. #9
    Join Date
    Mar 2011
    Posts
    28
    Would using "DefaultTeam" suffice for the TaskMatrix as attached?

    Also just realised I can't use 2x AutoNumbers in 1Table. And With each of these relationships do I want to enforce integrity?

    Additionally also realised that I'd like the EmpSkillsMatrix Tasks to be auto populated using the tasks entered from the TaskMatrix... and have them in the E.SkillsMtrx as a yes/no field.
    Attached Thumbnails Attached Thumbnails Untitled.jpg  
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  10. #10
    Join Date
    Nov 2011
    Posts
    413

    Example

    Let's say I have a customers table,a clients table and a time table(I have a TimeID in this table as my primary key) that I want to share data with,i.e. link tables.In my primary,lets say Time table I have a ClientID that links my Time table to my clients table.I also have in my Time table a CustID that links my Time table with my customers table.I can create a query that shows and or all of the fields in those table I want.Therefore I could easily make a form based on the query and put whatever fileds I want on it and of course do the same with a report based on the same query.

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by Range View Post
    Okay so I've had a little planning session in Excel (lols).

    Initially I thought I'd start with Tables as they seem to be the most important element when it comes to DBs and then upon approval I'll progress from there and revise based on any suggestions. Previously, I think I would have tried to spilt each team out individually but now I realise Access can handle just having 1 table as long as the links are correct.

    I'm thinking that the TeamID will be the main link between all 3 tables...? (DefaultTeam for TaskMatrix). And where I've put lookup next to the field I am hoping that team names/Tasks can just be lookedup from the tables to save on manually updating this when teams/tasks get added or removed. Appreciate any thoughts/suggestions/error highlighting...? Thanks

    I would create a task table which has all the task in it then I would creat a emptask

    Which would have the taskid and the empid in it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  12. #12
    Join Date
    Mar 2011
    Posts
    28
    I think this is what I have done in a round about way.

    Basically I've made a crosstab query of TaskMatrix combined with EmployeeSkillMatrix and then used the maketable function to auto include all of the tasks.... and then i'll change the field data type of these tasks to yes/no so each manager can highlight what skills their employees do and dont have...

    Is there a more efficient way of doing this? Was the way you mentioned much more efficient or pretty much the same thing?
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  13. #13
    Join Date
    Nov 2011
    Posts
    413

    Crosstab Query

    Not really,you will have problems since your tables are not really linked if I understand correctly.They need to be linked by ID as in the same unique ID in both tables.That way when you create a query and add those 2 tables,they will automatically be linked for you.Hang in there.

  14. #14
    Join Date
    Mar 2011
    Posts
    28
    Quote Originally Posted by myle View Post
    I would create a task table which has all the task in it then I would creat a emptask

    Which would have the taskid and the empid in it
    So you'd have 2x task tables? instead of the one TaskMatrix that that I was originally using?
    Access 2003 User.
    Limited experience with Access.
    Poor Coder.... pls be paitient with me!

  15. #15
    Join Date
    Nov 2011
    Posts
    413

    2 tables

    That would be my approach if I were doing it since almost always a second table is required for something.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •