Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8

    Autofill help!

    Hi all,



    I'm an Access newbie and I'm trying to create a database to replace a (horrible) excel spreadsheet which has been in my company for about 18 years.

    So the main table has a field for sample ID, and fields for each sample's associated chemistry. Each sample belongs to a drill hole, and some drill holes have 100 samples others have 5. The bulk of the data is entered by a lab who copy and paste from excel, and their sheets don't have the drill hole data, so this is always missing when they do the data entry. I was thinking there must be an easier way to enter the drill holes into the main table too rather than either manually or doing an excel sheet and copy/pasting it over, so I created a second table which has drill hole ID, sample from and sample to. E.g., drill hole DH1 contains from sample S001 to S100.

    Is there a way to make the drill hole ID on the main table auto complete? As in, can I have a formula saying "if a sample ID is entered, and is between S001-S100, drill hole ID = DH1?" I have no idea how to do formula/macro/VBA code in Access (I do have experience with all in Excel).

    And yes, unfortunately the hole ID has to be there on the main sheet as the geology department use the sheet and need to sort and filter using hole id.

    I hope I've explained everything ok Any help is appreciated.

    Thanks,
    EJ

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I don't see a formula for DH1. can you give it here?

    it looks like IF S### then
    random letter ,random letter ,#

    do you have a better one? Can you not use AUTOnum? I does give a #.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by EJ_ View Post
    I'm an Access newbie and I'm trying to create a database to replace a (horrible) excel spreadsheet which has been in my company for about 18 years.
    Quote Originally Posted by EJ_ View Post
    So the main table has a field for sample ID, and fields for each sample's associated chemistry.
    You are lucky, if I'm wrong, but it looks like you are on way to another horrible design. It looks like you have the table design like
    tblSamples: SampleID, DrillHole, SampleDate, Analyse1result, Analyse2Result, ..., AnalyzeSomeBigNumberResult;
    A simple question. What happens, when a new analyze is introduced?

    Instead you need 3 tables:
    tblSamples: SampleID, DrillHole, SampleDate, ..., Resolution, Comments;
    tblAnalyzes: AnalyzeID, AnalyzeText, [ResultType], ...;
    tblAnalyzeResults: AnalyzeReslultID: SampleID, AnalyzeID, AnalyzeResult.

    Quote Originally Posted by EJ_ View Post
    The bulk of the data is entered by a lab who copy and paste from excel, and their sheets don't have the drill hole data, so this is always missing when they do the data entry.
    Does this mean, that for every drill hole is a separate Excel workbook table with raw data? Then link those tables into Access database (you have to check the table structure, so that you are able to link the data at all). In Access, you create a procedure, which queries data form certain linked table (and adds drill hole number into query depending on which linked table is queried), and inserts into tblSamples samples from query not present in tblSamples currently. And after that processes all analyzes found in added sample, and for every analyze present adds a row into tblAnalyzeResults.

    You can have a button to run the procedure for all linked tables (for all drill holes), or you can write an Windows Sheduled Task which at certain interval (e.g. at every night, when nobody uses your Access DB) opens the database, runs the procedure(s), and then closes the database.

  4. #4
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8
    Quote Originally Posted by ranman256 View Post
    I don't see a formula for DH1. can you give it here?
    it looks like IF S### then
    random letter ,random letter ,#

    do you have a better one? Can you not use AUTOnum? I does give a #.
    Hi ranman256, there are no formulae anywhere in any table for anything because I don't know where to put them/how to do them in Access. The drillholes have more complex names than the example I used for "DH1," and are not in any numerical order.

    Quote Originally Posted by ArviLaanemets View Post
    You are lucky, if I'm wrong, but it looks like you are on way to another horrible design. It looks like you have the table design like
    tblSamples: SampleID, DrillHole, SampleDate, Analyse1result, Analyse2Result, ..., AnalyzeSomeBigNumberResult;
    A simple question. What happens, when a new analyze is introduced?
    Instead you need 3 tables:
    tblSamples: SampleID, DrillHole, SampleDate, ..., Resolution, Comments;
    tblAnalyzes: AnalyzeID, AnalyzeText, [ResultType], ...;
    tblAnalyzeResults: AnalyzeReslultID: SampleID, AnalyzeID, AnalyzeResult.
    The database is just for a current sampling program, it's extremely unlikely that any new analysis will start being done. I was only going to expand it to all sampling programs if it worked ok.

    Quote Originally Posted by ArviLaanemets View Post
    Does this mean, that for every drill hole is a separate Excel workbook table with raw data? Then link those tables into Access database (you have to check the table structure, so that you are able to link the data at all). In Access, you create a procedure, which queries data form certain linked table (and adds drill hole number into query depending on which linked table is queried), and inserts into tblSamples samples from query not present in tblSamples currently. And after that processes all analyzes found in added sample, and for every analyze present adds a row into tblAnalyzeResults.
    You can have a button to run the procedure for all linked tables (for all drill holes), or you can write an Windows Sheduled Task which at certain interval (e.g. at every night, when nobody uses your Access DB) opens the database, runs the procedure(s), and then closes the database.
    No, the drill hole data is nowhere except in the second table I made. But the first analysis to be done for any sample (density) has a separate raw data spreadsheet, as does all testwork done at my lab. How could I make a button to grab the data from these sheets and populate the Access sheet? I know this would be many steps but where do I start?

  5. #5
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8
    Never mind, I've found plenty of things on google about how to move spreadsheets into Access and where to go to write macros/VBA. Thanks for the help

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have to agree with Arvil. You are going from a (horrible) excel spreadsheet to a horrible, horrible, horrible Access dB.

    Access and Excel are two extremely different animals. Excel spreadsheets are typically short and wide. Access tables are typically tall and narrow.
    So, 1 Excel spreadsheet could be broken up into several Access Tables through a process called Normalization.


    It would help if you would post example Excel spreadsheet(s).



    Good luck with your project.........

  7. #7
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8
    I don't seem to be getting anywhere with my original plan, so sure.

    The spreadsheet is entered in by 2 labs and is then exported into different databases by geology (SQL and Access, I don't really know which they use for what). We are at a mining company located across different sites, me being the manager of lab2 - an analytical chemist with not a lot of Access experience. My problem is that the excel sheet is 18 years old, slow, has data validation problems which are always being manually corrected, and is constantly being accidentally tampered with by all 3 depots. We sometimes have network problems and aren't the most technologically advanced group of people. Long story short, I'm over fixing it all the time so want to move to something more secure with less data entry steps. Access is on my pc so I thought I'd have a go.

    Spreadsheet has 22 columns: Date received at lab 1, HoleID, SampleID, 3 x assays from lab1, 12 x assays from lab 2, date received at lab2. There are currently 3608 rows but this is just for the current program - there is a tab for every year since 1998 (ok...19 years old) but I just want the current data moved.

    Lab 1 have 2 separate spreadsheets for their 3 tests. Us at Lab 2 have a single spreadsheet. It'd be nice to have some sort of code to put all our data into the one database without having to touch it, have some way to validate it, and an easy way for geology to export it into theirs that doesn't involve them having to actually touch the data.

    Hope this helps

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by EJ_ View Post
    <snip> and is constantly being accidentally tampered with by all 3 depots. <snip>
    Been there - done that. In another life I worked with 3 other guys and had to enter production data every night. You get used to the order of columns and speed through the data entry.... EXCEPT when 2 of those "other guys" (the other week) would change the order of the spreadsheet columns!
    Screwed data up when entered into the wrong columns. So I created a database using Mac FileMaker Pro. Put a stop to changing things.


    Would you post the spreadsheet for 2019? Shouldn't be too many rows..
    Change any sensitive data first...

    Quote Originally Posted by EJ_ View Post
    <snip> 3 x assays from lab1, 12 x assays from lab 2 <snip>
    Could you/would you expound more about the assays?



    Quote Originally Posted by EJ_ View Post
    <snip> Lab 1 have 2 separate spreadsheets for their 3 tests. Us at Lab 2 have a single spreadsheet. It'd be nice to have some sort of code to put all our data into the one database without having to touch it, have some way to validate it, and an easy way for geology to export it into theirs that doesn't involve them having to actually touch the data.
    Do you know the table design(s) of the SQL dB and/or the Access dB that geology exports your data into?

  9. #9
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    Would you post the spreadsheet for 2019? Shouldn't be too many rows..
    Change any sensitive data first...
    2018/2019 data is all lumped together due to the continuation of the program from last year, so I've taken a section of data and put dummy numbers in. The numbers all reflect correct decimal places, and note that not every sample is assayed at Lab2:
    Click image for larger version. 

Name:	Minsamps.JPG 
Views:	22 
Size:	215.6 KB 
ID:	37089




    Quote Originally Posted by ssanfu View Post
    Do you know the table design(s) of the SQL dB and/or the Access dB that geology exports your data into?
    Finding that out tomorrow hopefully. I did make an excel macro which spat out a csv in the right format for their database, but I don't know which one it goes into or even if they still use it. I can do macros and VBA fine in excel haha.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Date received at lab 1,... , date received at lab2
    The image in previous post only shows one "Date Received" - for Lab 1? (since not every sample is assayed at Lab2)?
    Is there a date column for Lab 2


    Will there be other "compounds" that you (Lab 2) will test for?
    Last edited by ssanfu; 01-22-2019 at 11:23 PM. Reason: Added question

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by EJ_ View Post
    Hi ranman256, there are no formulae anywhere in any table for anything because I don't know where to put them/how to do them in Access.
    I think ranman256 did mean you didn't give the formula in your post Formulas or lookup fields in table is not very good idea!

    Quote Originally Posted by EJ_ View Post
    The database is just for a current sampling program, it's extremely unlikely that any new analysis will start being done. I was only going to expand it to all sampling programs if it worked ok.
    (This was about sample table structure.) New analyzes aren't only problem with spreadsheet-like structure. With normalized structure, you can use form-subform design to display analyze data. I.e. in parent single form you select a sample, and in continuous child subform all analyze results for this sample are displayed. You can also display additional information for every result, like limits, used analyze method, accuracy (not decimal places, but the result accuracy - how much the real value may differ from measured one) - any info you have in your database and can link with certain analyze. With your table structure, all info for analyze must be in single row, so any continuous forms are no-no by default even when you don't add additional info for every result!
    Also the spreadsheet structure makes it difficult to use the table in more complex queries, and excludes the possibility of using grouping feature for reports.
    And you are saying that in future this may change!? Then why not start with reasonable structure?

    Quote Originally Posted by EJ_ View Post
    No, the drill hole data is nowhere except in the second table I made.
    Is the table displayed in post #9 the one you generated, or the one you got from lab? There is the field HoleID - this is all hole data you need in this table. The rest of hole data belongs to tblDrillHoles.
    I have some questions with HoleID. It has format "CCyyyy_00".
    1. Has the string part "CC" any real meaning, or is this some characteristic only? I mean, can be there drill holes like AA2019_01 and BB2019_01, or not? When not, then maybe it will be better to have it as separate field in tblDrillHoles?
    2. Are the drill holes like CC2018_01 and CC2019_01 different drill holes made in year 2018 and 2019 respectively, or they are same drill hole and the year part is for sample table only? When later, then this part is abundant!

    Quote Originally Posted by EJ_ View Post
    The spreadsheet is entered in by 2 labs and is then exported into different databases by geology (SQL and Access, I don't really know which they use for what).
    Now this is most interesting part of the thread! As both Access and SQL are involved, then they may have SQL Server back-end database and Access front-end to work with it. When it is so, then all you need is the access to SQL database. Are you in same domain as this SQL database, or can you connect to this domain? It is much easier to read sample and analyze info from SQL database compared with reading this info from several Excel tables.

  12. #12
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8
    Quote Originally Posted by ArviLaanemets View Post
    I think ranman256 did mean you didn't give the formula in your post Formulas or lookup fields in table is not very good idea!
    Ah...makes sense!

    Quote Originally Posted by ArviLaanemets View Post
    (This was about sample table structure.) New analyzes aren't only problem with spreadsheet-like structure. With normalized structure, you can use form-subform design to display analyze data. I.e. in parent single form you select a sample, and in continuous child subform all analyze results for this sample are displayed. You can also display additional information for every result, like limits, used analyze method, accuracy (not decimal places, but the result accuracy - how much the real value may differ from measured one) - any info you have in your database and can link with certain analyze. With your table structure, all info for analyze must be in single row, so any continuous forms are no-no by default even when you don't add additional info for every result!
    Also the spreadsheet structure makes it difficult to use the table in more complex queries, and excludes the possibility of using grouping feature for reports.
    And you are saying that in future this may change!? Then why not start with reasonable structure?
    Hmmm...this makes me think that maybe Access is not the right thing for our lab. This is all what geology do with the data as they have much more information on each sample than us - drill hole info, the exact meterage where the sample was taken from, the mineralogy, the rock type, the geophysical data...etc etc. All we care about is the lab results. I just need a place to store our data that geology doesn't have to play with to copy it over, or a way for us to put the excel straight into their database.

    Quote Originally Posted by ArviLaanemets View Post
    Is the table displayed in post #9 the one you generated, or the one you got from lab? There is the field HoleID - this is all hole data you need in this table. The rest of hole data belongs to tblDrillHoles.
    I have some questions with HoleID. It has format "CCyyyy_00".
    1. Has the string part "CC" any real meaning, or is this some characteristic only? I mean, can be there drill holes like AA2019_01 and BB2019_01, or not? When not, then maybe it will be better to have it as separate field in tblDrillHoles?
    2. Are the drill holes like CC2018_01 and CC2019_01 different drill holes made in year 2018 and 2019 respectively, or they are same drill hole and the year part is for sample table only? When later, then this part is abundant!
    The table is the horrible excel sheet from the beginning of all the posts, the one where lab1 and 2 enter into and geology take data from. HoleID is in this table, but in my original post I was looking at a way for hole ID to be automatically filled into Access...before realising that that was the least of my problems

    Drill holes are in format CCYYYY_XX, most of them will have 2018 because that's the start of the program. The CC is important as it is the location of the hole and can contain 2 or 4 letters. XX is the hole number for that location. So for instance, NW2018_04 is the 4th drill hole for the north west map.

    Quote Originally Posted by ArviLaanemets View Post
    Now this is most interesting part of the thread! As both Access and SQL are involved, then they may have SQL Server back-end database and Access front-end to work with it. When it is so, then all you need is the access to SQL database. Are you in same domain as this SQL database, or can you connect to this domain? It is much easier to read sample and analyze info from SQL database compared with reading this info from several Excel tables.
    So their database is Maxwell, which is SQL back end, but at some stage Maxwell spits the data into Access before converting to SQL. I could connect to their SQL via our network but I know nothing about SQL so no point even asking questions about it.

    Quote Originally Posted by ssanfu View Post
    The image in previous post only shows one "Date Received" - for Lab 1? (since not every sample is assayed at Lab2)?
    Is there a date column for Lab 2


    Will there be other "compounds" that you (Lab 2) will test for?


    Sorry, yes, there are 2 columns the cutdown sheet misses out, date received and date assayed at Lab2. There won't be any other compounds tested for during the program.


    I think I'm barking up the wrong trees here and are in well over my depth. I reckon tidying up our excel sheet is probably the best way to go with the resources/time/knowledge I have, unless company wants to send me on an access/SQL training course haha.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So 3 date fields?
    date received at Lab 1
    date received at Lab 2
    date assayed at Lab2



    I think Access is probably the better solution, but to help you decide, you should work through the tutorials at Rogers Access Library.
    Take the time to actually DO the tutorials (3) - they should only take around 45 minutes each.


    I might play around with making a dB (just for fun..... yeah, yeah I know. I need to get a life )

  14. #14
    EJ_ is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Location
    Australia
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    So 3 date fields?
    date received at Lab 1
    date received at Lab 2
    date assayed at Lab2
    Yep.

    Quote Originally Posted by ssanfu View Post
    I think Access is probably the better solution, but to help you decide, you should work through the tutorials at Rogers Access Library.
    Take the time to actually DO the tutorials (3) - they should only take around 45 minutes each.


    I might play around with making a dB (just for fun..... yeah, yeah I know. I need to get a life )
    Thanks for the tutorial, I'll give it a go, I need to get out of my excel comfort zone.

    Haha, knock yourself out making a db! People do far sillier things for fun imho

    Thanks,
    EJ

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    When all you want to do, is to analyze existing data, then there is no need for separate full-scale database at all!

    1. You can use the same SQL database your geologists have, or you can only have access to this SQL database, and create (or let to create it by someone familiar with MS SQL) your own one (which contains only some views);
    2. You create SQL view(s) (in your database, when you decided to create one), which read(s) data from geologists database tables, and present this data in format which is best for your reports (e.g. using crosstable query, or adding calculated fields, etc.);
    3. You create a front-end application, which reads info from created views. Based on your last post, I suspect an Excel workbook which reads info from one or several SQL views and displays them (a query result table per worksheet) is all you need (You can use entries on report worksheet like month number in format yyyymm, drill hole ID, or whatever else fields you have in according view as parameters for query - whenever you change the value in such parameter entry, the data are requeried automatically). You can also have several different queries based on same view (raw data, aggregated data, different datasets, etc.). Based on read-in queries, you can make further calculations, Pivot tables, graphs, etc.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-14-2015, 02:29 AM
  2. Autofill
    By Jpxfit in forum Access
    Replies: 4
    Last Post: 12-29-2014, 03:57 PM
  3. Autofill?
    By LeadTechIG in forum Access
    Replies: 6
    Last Post: 12-05-2014, 09:18 AM
  4. Autofill
    By evosheas in forum Access
    Replies: 4
    Last Post: 09-20-2011, 02:29 PM
  5. Autofill
    By kdcooper88 in forum Access
    Replies: 1
    Last Post: 09-18-2010, 05:52 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