Results 1 to 12 of 12
  1. #1
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8

    MaxDate Help

    Good Day everyone,

    With reference to this thread https://www.accessforums.net/showthread.php?t=76607 which is quite similar to my issue, I would appreciate any help you fine people can provide.

    Im new to access and I am trying to create a Portable Appliance Database which will maintain a register of all tested appliances. For clarity what I have to do is import the .csv file from the testing machine everytime I want to update the database but I can't append the current table so just delete the old one and reference qryTableCopy to the new file. Then all my queries are based on the qryTableCopy.

    Im having an issue with "All Assets Date Due" Report which references the "qryAllAssetsDate Due". When the report is generated it will list mutiple entries for an asset if it was tested by a different person ie Electrician or ETO which I dont need. I only require the assetID to return the most recent test.
    Click image for larger version. 

Name:	1.JPG 
Views:	27 
Size:	26.9 KB 
ID:	38390




    PAT Testing.zip

    Any help would be very much appreciated and apologies for the messy database.

    Many thanks
    Paul

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    As start, it is a bad idea to import such info directly into table you are later working with. Currently you have a table which is refilled from csv-file. It's OK, and let it be so. But instead of working with this table, you create another table/tables. I advice tables, because the current table is not properly normalized.

    You have a table (I don't see field names in your picture, so I have to make some names up)
    tblCsv: DeviceID, DeviceType, Date1, Date2, Tester;

    You need to create additional tables:
    tblTesters: TesterID, TesterName;
    tblDevices: DeviceID. DeviceType,...;
    tblTests: TestID, DeviceID, TesterID, Date1, Date2 (I assumed Date1 and Date2 in your picture are linked to test. I hope at least one of tem is. In case another isn't, then it belongs probabli into tblDevices);

    No, in procedure which imports data into tblCsv, you add code, which (after the import is done):
    1. Updates the table tTesters with new names in field TesterName (It's best to have TesterID as autonumeric field);
    2. Updates the table tblDevices with new devices (adds DeviceID and DeviceType for which there is not the same deviceID in tblDevices;
    3. Updates the table tblTests with DeviceID, Date1, and Date2, and TesterID from tblTesters where TesterName is same as in tblCSV, wehere the combination of those 4 fields aren't present in tblTests;
    4. Deletes all records from tblCsv.

    So, you import csv-file, read imported info and distribute NEW info from it into 3 tables, and then you clear imported info, as it is not needed anymore.

    Now about your query:
    You have to create a saved query which calculates last test for every device. Like (I'm assuming Date1 is test date)
    Code:
    qLastTest = SELECT DeviceID, MAX(Date1) FROM tblTests GROUP BY DeviceID
    And now you create a query for your report like:
    Code:
    SELECT test.DeviceID, dev.DeviceType, test.Date1, test.Date2, tester.TesterName 
    FROM (((tblTests test INNER JOIN qLastTest q ON q.DeviceID = test.DeviceID AND q.LastDate = test.Date1) LEFT JOIN tblDeviced dev ON dev.DeviceID = Test.DeviceID) LEFT JOIN tblTesters tester ON tester.TesterID = test.TesterID)
    Last edited by ArviLaanemets; 05-13-2019 at 07:06 AM. Reason: I forgot to add the saved query to ensure only latest test for device is returned!

  3. #3
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8
    Hi Avril,

    Thank you for taking the time to respond. Goodness! Thats seems very complicated for what I was trying to achieve. Im not sure I have the knowledge to implement all that. Just to clarify, when I import the .csv I dont update a table, I create a new table and delete the old one. Is it possible to automatically update numerous tables by a single .csv import? If this is possible then I can understand how having more than one table would be manageable.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    I mean you use a procedure in Access table to import data - i.e. the import is initated from Access application. The same procedure then updates Access tables.

    Having Excel file instead of csv-file you could link it into Access database. Maybe it is possible with csv-file too, but I'm not sure - I haven't tried this.

    Having update source linked instead to have to load it has advantages - You don't have to bother with reading data into Access. You must have a source file with certain name in certain network location. The data in source file is overwritten periodically, or in case it supports ODBC queries (like Excel), it is read in from somewhere using query/queries.

    Having new data in your database (imported, or linked), you need a procedure which refreshes Access tables with new info (NB! Never use the table where you import new data, or which is linked with outside file, for something other except for updating other Access tables!). In case you import data with Access procedure, you can have the code for updating in same procedure. Otherwise (this covers also the case where you import new data into Access from outside) you need a special procedure which does updating (OnClick event for some button on form, or OnOpen event of your main form, or a procedure run from Window's Task Scheduler - the choice is yours).

    All this may look difficult at first glance, but this ensures you have less probability to have problems in future. It is very easy to corrupt your database so you have the only option to rely on saved backup, when updating your database with outside data. Having imported data separated from other database tables, and checking the validity of imported data in update procedure lowers the risks considerably.

  5. #5
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8
    I dont know Avril, my database works fine aside from this small or what I assumed was a easily solved issue. I really dont want to over-engineer such a simple task. Ill look into creating a primary key on my imported table giving each test a unique value and somehow create a relationship between 2 queries that will give me the results I require.

  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
    Yes, a CSV text file can be linked. If you gave the file a generic name and just replaced the file each time data is pulled from wherever, retaining generic file name, the link will be stable (as long as field names are same).

    qryMaxDate2 includes UserNotes and TestedBy in GROUP BY - probably should exclude them. If UserNotes had data, each record would probably be unique and no aggregation would occur.

    Table has 1640 records, aggregation reduces to 1481. There are actually 1450 unique AssetID values. 31 AssetID's are duplicated in the query. So is the aggregation showing correct data? Removing TestedBy field returns 1451 records. AssetID DT9 has two different asset descriptions. Fixing that will result in 1450 records in query.

    Advise not to use spaces nor punctuation/special characters in naming convention.
    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
    Join Date
    Apr 2017
    Posts
    1,673
    I missed before the attached example file. Now I used this to demonstrate, how updating of Access tables from imported/linked table works (I did stop after assets table, as I could not spend more time on it at moment, but I think you get an idea).

    I created some tables I did feel are needed, and did set field formats I felt reasonable on base of data present in your table. Then for every of those tables I created an INSERT INTO query, which appends from your table new info into defined table. And then I created a module with procedure, which runs those queries in right order. You can run this procedure from some event. As you see, the amount of code needed is minimal.

    NB! I found, that asset ID 'DT 9' was in your table with 2 different descriptions. I replaced one of ID's with 'DT 9x', as otherwise it was not inserted into tblAssets.
    Attached Files Attached Files

  8. #8
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8
    Wow! Thank you Avril that looks like a lot of effort on your part and I really appreciate the help. At the moment I dont fully understand what exactly you have done but I can see you have created tables which can be updated from a single csv file which is great. Ill spend some time on this trying to understand how to achieve what I want from the information you have given and in the process learn a helluva lot!

    Thank you

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you see post 6?
    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.

  10. #10
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8
    Quote Originally Posted by June7 View Post
    Did you see post 6?
    hi June sorry I didn’t ..oops! Thank you for taking the time. Both you and Avril have taken considerable time out of your day to help but unfortunately I can’t see an answer within the replies as they are far beyond my capabilities I would have thought a table that uses 6 fields could easily be reduced to the most recent record for each AssetID but clearly it’s not easy.

    i fully appreciate your efforts in trying to help though

    paul

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The most recent record is not what your query accomplishes. An aggregate (GROUP BY) query is intended to summarize data - generate statistics like average, sum, count, standard deviation, maximum, minimum - and is relatively simple.

    If you want the most recent record for each AssetID and you want all fields, that gets more complicated. Review http://allenbrowne.com/subquery-01.html#TopN

    Your source table has more than 6 fields. Your query pulls 6 of those fields but as I describe in prior post, that is too many. And there was a data error.

    SELECT DISTINCT query is a variation on aggregation, just doesn't generate any statistical info. Again, you pull too many fields for each record to be "distinct".
    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.

  12. #12
    leckytech is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2019
    Posts
    8
    Thanks June, Ill look into that Allen Browne tutorial which looks interesting. A crash course in SQL would seem appropriate!

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

Similar Threads

  1. maxDate sum
    By ShostyFan in forum Queries
    Replies: 3
    Last Post: 06-30-2015, 11:11 AM
  2. MaxDate From Query
    By Mpike926 in forum Access
    Replies: 4
    Last Post: 01-28-2015, 08:33 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