Results 1 to 11 of 11
  1. #1
    Bradders92 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2023
    Posts
    5

    Starting from scratch Excel to Access

    Hi all,

    Recently signed up as I've now got to make a switch from Excel to Access, I am unsure if what my 'blue sky' idea is possible and I've read over this forum numerous times and seen everyone is incredibly helpful and knowledgeable.

    I've taken over a position within a small business that handles cargo as a freight forwarder. A cargo manifest is produced on Excel through manual inputting at least 200 rows of individual pieces of data. The Excel Worksheet has the Column headings;

    - Box ID Number
    - Shipment ID Number
    - Box Destination
    - Item Description
    - Length
    - Width
    - Height
    - Volume
    - Weight
    - Status

    I've introduced the use of Scan guns that allows me to now run 3 sheets;
    - Goods Expected (Inputted Manually by our order team) // Box barcode is scanned in and it places a "Received" into the Status column
    - Warehouse // Any goods with a "Received" in the status Column, a macro will move each row from the "Goods Expected" sheet to the "Warehouse" Sheet
    - Final Cargo Manifest // We manual input "Dispatched" into the Status Column if we've loaded it to a vehicle/container. This is then printed and given to our drivers/hauliers to as the completed manifest

    This is working but I feel we're on a very precarious set up and reading this forum has given me an idea that Access might be the way forward as a more robust option. My thinking is for an Access Solution I could produce a system with a good user interface and database that I can track items in the Warehouse. Could anyone advise if the below is possible within the parameters of Access 2016;

    1. A Form/Subform that our Order team can use to create a Shipment ID then add multiple rows/records into of Box IDs (i.e. Enter a Shipment ID at the top of the form then have a mini table of all the sub headings such as description, length etc) so they're not having to stare a masses of lines we've had a few hiccups recently. All Box IDs are unique and never repeat themselves.

    2. Scan gun integration I'm using VBA/Macros to achieve this but can Access mimic what Excel is doing in a better way?

    3. Export the Access Data to Excel in a better format that we can also send via email?



    4. Box ID/Shipment History searches so I could look back and see when a shipment was moved to the final manifest/Dispatched?

    I know everyone wants examples to work from but at the moment I'm staring at a blank Access 2016 database with Youtube as my guide but I can't help but feel lost without knowing if my 4 questions are actual possibilities.

    Any help would be greatly appreciated, thank you for taking the time to read my post.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. yes, form/subform sounds appropriate - unique ID can be generated with autonumber type field but if cannot allow gaps in sequence might need to construct custom IDs with VBA. What does "stare a masses of lines" really mean?

    2. yes, scan gun can be an input device

    3. yes, export data to Excel or save reports as PDF

    4. yes


    A solid week with an introductory tutorial book could be beneficial.
    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.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Just a heads up.
    Database(Access or any RDBMS) is very different that spreadsheet (Excel).

    The recently released Northwind 2 Starter Edition would be a good intro to some features of Access.

    There are a number of links to articles/videos in my signature Database Planning and Design
    -You may find the youtube series by Steve Bishop very useful
    -the intro to database overview by guru99 and intro to ms Access

    The short videos by BA_Experts are humorous and informative.

    Good luck with your project.

  4. #4
    Bradders92 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2023
    Posts
    5
    Hi June/Orange,

    Thank you for your replies I've add some further questions in red to your answers

    Quote Originally Posted by June7 View Post
    1. yes, form/subform sounds appropriate - unique ID can be generated with autonumber type field but if cannot allow gaps in sequence might need to construct custom IDs with VBA. What does "stare a masses of lines" really mean?

    So the excel Worksheet view currently has around 500-700 rows of information with those column headers I outlined earlier, it's incredibly hard for someone to look at and check/verify and also prone to incorrect data input. We've had lines overwritten by accident previously which caused a huge problem thus using the forms/subforms would eliminate most of the issues. An example is Box IDs are in the format of ABC-D123456, ABC-D213456, BAC-D654321 etc along with more numbers can cause issues.

    We put Box IDs into Shipment ID Groups so for example we could have 10 Box IDs in 1 Shipment ID, is it possible that our order team could just input/generate the Shipment ID first into a box then with the form add the Box ID records with the dimensions, weights in predefined columns that then feed into a master table (i.e. my Goods Expected)?

    2. yes, scan gun can be an input device

    I'm assuming with a bit of tweaking, our scan gun could scan in the Box IDs (lets say 200 Box IDs) and move them from one data sheet to another in quick succession? I'd play in VBA to work it out eventually like I did with Excel.

    3. yes, export data to Excel or save reports as PDF

    4. yes


    A solid week with an introductory tutorial book could be beneficial.
    Could you recommend a book that I'll purchase?
    Orange, those website links are great too I'll start reading up.

    As another question if I got my order team to have separate Excel docs that we defined the columns to match Access Field IDs how well does data 'import' from Excel into Access?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. yes, the more 'user-friendly' the more code

    2. not sure why would need to 'move' IDs


    I don't have a specific book in mind as it's been so long since I referenced one. Buy several old, used ones for cheap, just make sure they are for Access 2010 or later.
    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.

  6. #6
    Bradders92 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2023
    Posts
    5
    Quote Originally Posted by June7 View Post
    1. yes, the more 'user-friendly' the more code

    2. not sure why would need to 'move' IDs


    I don't have a specific book in mind as it's been so long since I referenced one. Buy several old, used ones for cheap, just make sure they are for Access 2010 or later.
    In response to question 2, I only ask about that as that’s how I’ve got the Excel sheet to work so I get the Box IDs to move through from being expected to warehouse to dispatch as to track the box location and then have a history at the end.
    Access can probably do this smarter?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have a field that indicates status. Or several columns to record date of status. Could have done same in Excel.
    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.

  8. #8
    Bradders92 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2023
    Posts
    5
    Quote Originally Posted by June7 View Post
    Have a field that indicates status. Or several columns to record date of status. Could have done same in Excel.
    I’m assuming macros and form/subforms this can be managed. For example different buttons on a form will change field status etc

  9. #9
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    "Status" fields can be a bit of a rabbit hole.

    We used to keep a status record against a job, which was a child table and recorded the Status, Date/Time of entry and who added it, along with a optional note text. This gave you a timed history per job.
    You can add as many status records as required with this method and find it's current status from the last timed entry at any time.
    So for instance on receipt into the building a status was added automatically of "Booked In".

    You can further tweaks to only allow certain statuses to be assigned by certain staff. Make it as complex or simple as you need.
    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 ↓↓

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Quote Originally Posted by Bradders92 View Post
    I’m assuming macros and form/subforms this can be managed. For example different buttons on a form will change field status etc
    Buttons or combobox.
    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.

  11. #11
    Bradders92 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2023
    Posts
    5
    Thanks all for the advice and to the people who’ve also sent private messages offering to help too.
    I feel less daunted on doing something with access and now know it’s the right tool for our warehouse.
    I’m going to have a meeting with all involved to see if we can make it work and reduce repetitive work and manual inputs where necessary

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

Similar Threads

  1. Starting from scratch in 2022
    By TOPSie in forum Access
    Replies: 1
    Last Post: 07-28-2022, 05:48 AM
  2. Replies: 36
    Last Post: 03-15-2021, 01:16 AM
  3. Replies: 2
    Last Post: 07-11-2014, 09:43 PM
  4. Replies: 1
    Last Post: 10-28-2013, 12:32 PM
  5. Access Novice - Looking to learn from scratch.
    By Javaman in forum Database Design
    Replies: 1
    Last Post: 03-13-2010, 08:53 PM

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