Results 1 to 7 of 7
  1. #1
    FFN is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    8

    Building a large Database(s)..Needing Some Help!

    Hello!



    This will probably be lengthy, so please bear with me.

    At my job we are needing to find a way to stream-line our data entry and keep things more organized and also we're wanting to use less paper (because it's 2018 and I'm mostly tired of filing that crap away).

    My boss has tasked me with learning Access, which I'm actually enjoying for the most part. I've taught myself to building forms and what not and even done a bit of very light programming to get a form to hide/show a text box if a checkbox is checked for certain things.

    The point of this database (aside from just being more organized) is so that we don't have to type the same thing more than once. We want it to where if we type, let's say, a Contract Number in one database/table, we can just refer to it and pull that information into another.

    What we're trying to figure out now (and what I have not been able to find anywhere on the net) is if this is possible;

    Is there a way to make it to where if we type something in one database, we can reference it or pull data from a specific field/database/table to another one and never have to type the same piece of data more than once?

    ...let's say I have "Jobs Database" wherein there is a table called "Jobs" like below:

    JobNumber Item Quantity
    18001 Screen 2
    18002 Board 4






    and in a separate database called "Purchase Orders" (where we keep track of purchases from different vendors and the materials we had to use to make a certain part for the Job, which ties back to the JobNumber within that database) there is a table called PONumber.

    Purchase Order Database:

    PONumber JobID
    155 18001
    154 18002





    Now, my thinking was that I could somehow go into the Purchase Order Database and have the PO table and a field called PONumber, and then create a link to the Jobs database in the Purchase Orders database.

    So I did just that.
    After I did this, I made a field called JobID, and then I used the Lookup Wizard to lookup the field of JobNumber from the Jobs table, therefore linking JobNumber to JobID in the Purchase Orders Database.

    So upon doing that it brings over the data I've entered in that table, and simply allows me to select from the records there (in this case 18001 and 18002).

    Again, what we're wanting to do is have to where, if I type it once, I never have to type it again.

    Is this possible at all? Or am I just going to have to make sure the Purchase Order Numbers and the Job Numbers are all where I need them to be at all times?
    This is a really dumbed down situation, but is more or less what I'm working with.

    Because linking to this table, and then having to manually go in and select all of this stuff is fine and all. But I think my boss is wanting it to be more automated than it looks like it's going to be.

    Anyway, thank you if you made it this far! It's a big project and the more I research and learn, the less I think that we're going to get exactly what my boss wants lol

    I look forward to some replies!

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Firstly if you the PO's and Job databases are going to interact a lot I'd be tempted to have them in the same database.
    Unless by database you mean a Table?

    Secondly you would have to select the JobID against the PO manually at least once somewhere in the process, as unless they have suddenly added an omnipresence function to Access it would have no way of "knowing" that that PO was for that JobID.

    Unless of course you created the PO from the Job form, in which case you could create and store the linked fields between the two automatically.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Minty -- you may be confusing database with table. Before jumping into a physical Access database, spend some time to learn and understand some database concepts. Database is not the same thing as MS Access.

    Here is a link to some articles on Database Planning and Design (and more). I recommend you work through some of the tutorials from RogersAccessLibrary mentioned in the link. But, do that after watching the Dr. Soper videos for concepts.


    Good luck with your project.

  4. #4
    FFN is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    8
    Hah! Yes, that's actually kind of what I was trying to jokingly explain to him yesterday. "Technology is cool and all...and I can learn this program, but it's not going to be able to read our minds no matter how much we mess with it," is more or less what I said.

    And yeah, something I had thought about was just...making this all one database. Because, atm, I have it spread out in separate databases and in those database I have the tables for each thing. The reason I did this is because I read that access only allows like 2 GB per each database? And I know that, once we get this going and start getting everything in there, we'll surpass that 2 gb fairly quickly.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I doubt you will reach that limit as quickly as you might think. Many on the forum have relatively large databases without space issues. Design it as one logical database from the beginning.

    You can build a data model with pencil and paper; test it; adjust as needed to get a "blueprint" for your development.
    I am going to offer another link to some info I provided to another poster in a similar situation. Stump the model.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think SQLServerExpress has 10gb limit and is free. If you outgrow that might consider paying the big bucks for SQLServer.

    Whatever used as backend, Access can be frontend.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    FFN is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Aug 2018
    Posts
    8
    Thank you all so much!

    That all cleared things up and made things a little easier on my end for sure.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 10-21-2017, 04:05 PM
  2. Replies: 12
    Last Post: 03-01-2017, 10:39 PM
  3. Replies: 4
    Last Post: 12-14-2016, 08:23 AM
  4. Needing help with database setup.
    By roger123 in forum Access
    Replies: 3
    Last Post: 06-21-2012, 08:21 AM
  5. Novice User Needing Help Building a Database
    By softspoken in forum Database Design
    Replies: 1
    Last Post: 10-11-2011, 09:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums