Results 1 to 13 of 13

Creating a specific search DB

  1. #1
    ErikE is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5

    Creating a specific search DB


    Hello

    Doing what I need in Excel would be a bit of a nightmare, since I would have to list every cylinder to make them searchable. I’m new to access and don’t know much about the program, but I hope you are willing to help me a little. We retest cylinders at my plant, but on the older cylinders the wall thickness is not stamped and we need the wall thickness for our ultrasonic tester. What I need is a database for gas cylinders, more specific linking wall thickness to an identify number without having to list every identify number in the list. Would it be possible make a list like this:

    ID number Producer Wall thickness Gas service Production date
    112000-113000 Worthington 5.4 mm Oxygen 10.1995
    245650-245800 Heiser 6 mm Nitrogen 06.1989
    112500-113500 Chesterfield 4.5 mm Helium 12.1995
    58450-59000 Chesterfield 5.4 mm Oxygen 04.1984
    320200-320400 Eurocylinder 6 mm Hydrogen 01.2001

    So when I make a search 112059 I will get the first line. If you notice, there are two lines with overlapping numbers. If I make a search like 112598 I will get both lines to show up in the search.
    Is this possible to make, or do I need to write down every cylinder in a list?

    We use office 365 at work, so I guess that is the 2016 version of access.

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,547
    if you split your id number into two IDFrom and IDTo

    you can then make a query

    SELECT *
    FROM myTable
    WHERE [Enter ID] Between IDFrom and IDTo
    you will also need a separate PK (Primary Key) autonumber field and your two new fields need to be indexed for good performance

  3. #3
    alansidman's Avatar
    alansidman is offline Mountain Living Proponent
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,429
    ID cylinderNumber Producer WallThickness GasService ProductionDate
    1 112000 Worhington 5.4 mm Oxygen 10/1/1995
    2 113000 Worthington 5.4 mm Oxygen 10/1/1995
    3 245650 Heiser 6 mm Nitrogen 6/1/1989
    4 245800 Heiser 6 mm Nitrogen 6/1/1989
    5 112500 Chesterfield 4.5 mm Helium 12/1/1995
    6 113500 Chesterfield 4.5 mm Helium 12/1/1995
    7 58450 Chesterfield 5.4 mm Oxygen 4/1/1984
    8 59000 Chesterfield 5.4 mm Oxygen 4/1/1984
    9 320200 Eurocylinder 6 mm Hydrogen 1/1/2001
    10 320400 Eurocylinder 6 mm Hygrogen 1/1/2001

    Build a table as above then build a query as follows:

    Code:
    SELECT Table1.ID, Table1.cylinderNumber, Table1.Producer, Table1.WallThickness, Table1.GasService
    FROM Table1
    WHERE (((Table1.cylinderNumber) Like [Enter first 3 digits] & "*"));
    
    Sample DB attached.
    Attached Files Attached Files

  4. #4
    ErikE is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    Thank you for the answers. I will try to get this working. Total noob, so hope I will manage it.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    It seems producer gives a distinct result for all cases except "Chesterfield".
    You also show gas service is that relevant to your needs?

  6. #6
    ErikE is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    Gas service is a nice to know information. The only thing I want to be searchable is the cylinder number without having to list every cylinder in that series, since it can be hundreds of them. The thing is that we now go in to the paper documentation of our older cylinders to find the wall thickness and write the series down on a paper. Over time, that paper becomes a large document we have to search through, to see if we previously have checked for wall thickness on that specific series. Would save some time for the operator if he can just type in the cylinder number he have in front of him and get up blank if we don't have it or a line that tell us what data we put in by making it possible to search. The nice to know info is in the table so that when the organisation send me questions, I can go in to the DB, rather than going in to the paper archive to find the answer.

    alansidman added DB for me to look at and that was very helpful, that way I could see how he built it up. Only problem is that searching in that table will not be optimal, since we have several series that start with the same 3 digits and setting up the table like that, I will not know the end of the series (guess I can fix that by putting up a line that is not searchable and just put in the end of the series there).

    Ajax sugestion seems like a good alternative also, but my noobness is hindering me on the query part, but I will try to figure it out.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    Part of the problem(in my view) is a clear definition of the issue to be resolved.

    You have a number of gas cylinders.
    Each cylinder is produced by a Manufacturer.
    Each cylinder has a specified wall thickness.
    Each cylinder has an Identification number.
    Cylinder Identification numbers are listed as a Range of Identification numbers.
    Cylinders in Each Range of Identification numbers has an associated Production Date.
    Cylinders in Each Range of Identification numbers have a specified Gas Service.

    First problem is that identification number ranges overlap in at least 1 case(based on your sample).
    So identification number alone does not identify a specific cylinder in all cases.
    Code:
    To find the wall thickness of  cylinder xyz, the steps involved would seem to be:
    
    -who is the manufacturer(search)
    -within that manufacturer what is the Identification Number(search)
    -display wall thickness and other details
    
    OR
    
    -enter the Identification number
    -if not an overlapping range then
       display wall thickness and other details
    -if overlapping, then
       enter the Manufacturer to resolve the overlap, then
       display wall thickness and other details
    The bigger question may be how you will record the data in your table(s) since
    can be hundreds of them
    .
    Who will use the application.

    Seems you could mock up a search form with some sample data, review same with colleagues and refine your development. Either approach, Ajax or Alan, would get you what you need more efficiently that searching through paper records.

  8. #8
    ErikE is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    I was actually able to get Ajax approach to work and it do what I want it to do. I can search what ever number I want and I get all the info I have put in. With overlapping numbers i get all the lines up. This is just what I needed.

    The reason I need the producer is that it will help the operator pick the right series, since the same producer don't have overlapping numbers. Gas service will also help with that. The producers are sending us certificates on the series we have bought and the wall thickness will be the same on all within that series/batch.

    So with what I have now, I will be able to make a nice DB for my need. Whats frustrating for me is that I don't really know whats possible. Now that I have the basic DB worked out, I would like to have a search field up at all times that you can type in to and it will show hits with in that search. The search window will stay and I can make a new search directly, without having to actually go in to the program like I'm now.... If I make any sense. The ones who are gone use it to search, don't even know what access is. Maybe I waste my time one this (and yours). Basic Access makes basic Excel look like figuring out the rules in Ludo!

    I'l upload my DB when I'm back at work tomorrow

  9. #9
    alansidman's Avatar
    alansidman is offline Mountain Living Proponent
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,429
    I have adapted the attached DB to run AJAX' SQL statement. Open the DB and it will open with a form that all that is required is to click on the button to enter a serial number and the query will run for you.
    Attached Files Attached Files

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,547
    happy to help!

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287

  12. #12
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,547
    I would like to have a search field up at all times that you can type in to and it will show hits with in that search. The search window will stay and I can make a new search directly, without having to actually go in to the program like I'm now...
    all things are possible. Probably in this case, you would have a search form as a popup, then you can hide the app and the popup remains on the desktop. Needs some coding though

    see this link
    https://www.access-programmers.co.uk...d.php?t=217400

    this link shows a way without coding
    https://accessexperts.com/blog/2015/...cess-tutorial/

  13. #13
    ErikE is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    5
    Thank you for the help and tips, really appreciate it and thank you alansidman for putting it together for me. I have some time in front of me to learn to do this myself, but I have to say, Access is a bit overwhelming.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-30-2016, 03:41 AM
  2. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  3. Search for a specific Record
    By RayMilhon in forum Forms
    Replies: 1
    Last Post: 12-04-2013, 02:28 PM
  4. Replies: 2
    Last Post: 08-31-2010, 08:57 PM
  5. Replies: 3
    Last Post: 01-14-2010, 08:32 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
  •  
Tech Forums: Microsoft Office Forums