Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    How to limit multiple records against a unique Job# (summary)?

    We are tracking a service contract. Each job# (an alphanumeric) is a unique number. However, there can be multiple visits against the same Job# (for example, annual contract for a weekly mowing service).



    In my Form, I have been able to summarize the various costs (equipment, materials and labor) for all the visits, but I need to be able to show only one Form/document and not the multiple visits. Right now, for each unique Job#, I get all the visits against that Job# number. What VBA code would limit each Job# to only one (Summary) view?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    all depends on your table design - based on your brief description you should have two tables, one for jobs and one for visits which have a one to many relationship. Probably more for costs/equipment/materials etc. Your form would then just be based on the jobs table

  3. #3
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @Ajax

    Yes, I have 2 tables and the Form that I am using is based on Table2. What code would limit the view of Job# to a "summary" view, no duplicates?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    provide an example of what you have now and what you want to see as a summary. At the moment the only suggestion I can make is use either SELECT DISTINCT or a GROUP By query - both of these cannot be updated so if you want to be able to do that then it would not be a solution

  5. #5
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Click image for larger version. 

Name:	Job# limit .JPG 
Views:	42 
Size:	117.1 KB 
ID:	42587
    This is the form and I have all the cost information summarized, already. So, what I want to see is only one view (and not in multiples of work done against this Job#). So, what code can I insert in the Job# field to provide that one view (no duplicates)?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I really mean't the data - i.e the recordsource to your form and a screenshot of the form when open so I can see some values. You know your application, I don't. I suspect from what you have provided that your tables and relationships are not constructed correctly - you should not need all those dsums.

  7. #7
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Click image for larger version. 

Name:	ERic Tables1.JPG 
Views:	40 
Size:	119.3 KB 
ID:	42591@Ajax

    Please see attached
    Record Source: SELECT TABLE2.ActualDate, TABLE2.[Account#], TABLE2.BusinessName, TABLE2.[Job#], TABLE2.LABOR_COST, TABLE2.EQUIPMENT_COST, TABLE2.MATERIALS_COST, TABLE2.[TOTALJOB_COST], TABLE2.[House#], TABLE2.StreetName, TABLE2.Town, TABLE2.[Phone#], TABLE2.JobType, TABLE2.JobDescription, TABLE1.TotalQuote, TABLE1.LABOR_COST AS LABOR_COST_TABLE1, TABLE1.QUOTATION_COST, TABLE1.EQUIPMENT_COST AS EQUIPMENT_COST_TABLE1, TABLE1.MATERIALS_COST AS MATERIALS_COST_TABLE1, TABLE1.TOTALJOB_COST AS TOTALJOB_COST_TABLE1, TABLE2.ContractorComments, TABLE1.DateTimeStamp FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.[JOB#] = TABLE2.[Job#];




    Click image for larger version. 

Name:	Eric Form1.JPG 
Views:	44 
Size:	130.7 KB 
ID:	42588Click image for larger version. 

Name:	Eric Form2.JPG 
Views:	42 
Size:	128.3 KB 
ID:	42589
    Last edited by zkrucz; 08-07-2020 at 11:56 AM. Reason: Corrected TABLES with 1 to many link

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you appear to be duplicating data between table1 and table2. Account#., businessname, job#, town, to name just a few

    Without sensible table names, difficult to work out what is what but assuming table1 is about the job and table2 components of the job, you should be using your account table as your record source- with everything else being calculated as a group by query, summing things like costs - and bringing fields through like actual date, contractor comments, timestamp will inevitably mean you will still have duplicates - the only way to eliminate those would be to bring through the first/last/max or min value for these fields

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, your field names need work.

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    ( You have "#" in many field names)

    "Form", "System", "Date" and "Day" are reserved words in Access and shouldn't be used for object names.


    Maybe you would post your dB? (remove/change any sensitive info, do a "Compact & Repair", then compress/Zip))

  10. #10
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Hey AJAX and ssnafu,

    I scrubbed my Tables to try to conform to accepted standards. I have the file zipped but I don't see the "attach file" icon, above. Please advise.

    Regarding the "duplicate" fields between Table1 and Table2, they are not really duplicates. Both tables are populated by information coming from our techs using iPads. Table1 reflects quotations. A lot of that information from Table1 is then dispatched to those iPads and then sent back (with additional info) and populates Table2, as work orders (and there may be many work orders against a unique JobNo).
    Attached Files Attached Files

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my comments in post#8 still stands - use a group by query and either exclude or use min/max etc for the fields causing your duplication

  12. #12
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @Ajax,

    I am not familiar with that query. But, how would it be used in the expression in the form to group the JobNo field?

  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
    I modified your table designs and relationships the way I would have designed them - for the most part.

    Looking at the fields in tables Table1, Table2 and Table3, I would have say you started out with Excel spreadsheets and imported then into Access.
    Those tables have fields where the field names are actually data. Plus you cannot easily expand.....

    Here is my designs/Relationships
    Click image for larger version. 

Name:	Relationship1.png 
Views:	28 
Size:	212.1 KB 
ID:	42637
    Table1, Table2 and Table3 are poor names - tblQuotes , tblJobs and I don't know what Tabel3 is for....


    The reason the current design is poor is because
    Click image for larger version. 

Name:	Relationship2.png 
Views:	30 
Size:	78.8 KB 
ID:	42638
    You would have to add at least 2 fields, then edit the queries, edit the forms and edit the reports.
    If you add a third crew, you have to do the same thing.


    Anyway, here are my changes. I renamed fields and changed the PK/FK fields in the tables to properly link the tables.
    I changed the "#" and changed field names with "No" to "Num". (Account No??? Can there be an Account Yes?)

    I underlined "business Name" because it is in 3 linked tables - duplicating data.
    Attached Files Attached Files

  14. #14
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    @ ssnafu

    Thank you for making all those changes. I am getting a real education here on Access.

    So, after all said and done, can the "duplicates" under JobNum in the Account Summary Form be eliminated with an expression?

  15. #15
    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 zkrucz View Post
    Regarding the "duplicate" fields between Table1 and Table2, they are not really duplicates. Both tables are populated by information coming from our techs using iPads. Table1 reflects quotations. A lot of that information from Table1 is then dispatched to those iPads and then sent back (with additional info) and populates Table2, as work orders (and there may be many work orders against a unique JobNo).
    OK, so maybe Table1 is for Quotes and Table2 is for Work Orders per JobNum.



    Quote Originally Posted by zkrucz View Post
    So, after all said and done, can the "duplicates" under JobNum in the Account Summary Form be eliminated with an expression?
    I was only concerned with trying to get the table structures /relationships in better shape. You still should normalize at tblAccounts, tblTable1 and Table2.


    I am not sure where you are headed with the query or what you want to do with it. Remember, you have been working on the dB for a while and we have just seen a minute of it.
    Here is the query that works with the attached dB - it is saves as Query1.
    Code:
    SELECT tblTABLE2.Table2ID_PK, tblTABLE2.Table1ID_FK, tblTABLE2.ActualDate, tblTABLE2.[AccountNum], tblTABLE2.BusinessName, tblTABLE2.[JobNum], tblTABLE2.LABORCOST, tblTABLE2.EQUIPMENTCOST, tblTABLE2.MATERIALSCOST, tblTABLE2.[TOTALJOBCOST], tblTABLE2.[HouseNum], tblTABLE2.StreetName, tblTABLE2.Town, tblTABLE2.[PhoneNum], tblTABLE2.JobType, tblTABLE2.JobDescription, tblTABLE1.TotalQuote, tblTABLE1.LABORCOST AS LABORCOSTTABLE1, tblTABLE1.QUOTATIONCOST, tblTABLE1.EQUIPMENTCOST AS EQUIPMENTCOST_TABLE1, tblTABLE1.MATERIALSCOST AS MATERIALS_COST_TABLE1, tblTABLE1.TOTALJOBCOST AS TOTALJOB_COST_TABLE1, tblTABLE2.ContractorComments, tblTABLE1.DateTimeStamp FROM tblTABLE1 LEFT JOIN tblTABLE2 ON tblTABLE1.[Table1ID_PK] = tblTABLE2.[Table1ID_FK];

    Here is the query reformatted for read ability:
    Code:
    SELECT 
    tblTABLE2.Table2ID_PK, 
    tblTABLE2.Table1ID_FK, 
    tblTABLE2.ActualDate, 
    tblTABLE2.AccountNum, 
    tblTABLE2.BusinessName, 
    tblTABLE2.JobNum, 
    tblTABLE2.LABORCOST, 
    tblTABLE2.EQUIPMENTCOST, 
    tblTABLE2.MATERIALSCOST, 
    tblTABLE2.TOTALJOBCOST, 
    tblTABLE2.HouseNum, 
    tblTABLE2.StreetName, 
    tblTABLE2.Town, 
    tblTABLE2.PhoneNum, 
    tblTABLE2.JobType, 
    tblTABLE2.JobDescription, 
    tblTABLE2.ContractorComments, 
    
    tblTABLE1.TotalQuote, 
    tblTABLE1.LABORCOST AS LABORCOSTTABLE1, 
    tblTABLE1.QUOTATIONCOST, 
    tblTABLE1.EQUIPMENTCOST AS EQUIPMENTCOST_TABLE1, 
    tblTABLE1.MATERIALSCOST AS MATERIALS_COST_TABLE1, 
    tblTABLE1.TOTALJOBCOST AS TOTALJOB_COST_TABLE1, 
    tblTABLE1.DateTimeStamp 
    
    FROM tblTABLE1 LEFT JOIN tblTABLE2 ON tblTABLE1.[Table1ID_PK] = tblTABLE2.[Table1ID_FK];
    I converted the query into a Main Form/ Sub Form arraignment - "frmForm1" (and sub form "sfrmForm2").

    Still mot sure where you are headed with it.


    Maybe you could tell us more about the process re: "techs using iPads". Because of the tables not being normalized and the duplicating of data in Table1 and Table2, you might be in for writing a lot of UDFs.
    Attached Files Attached Files

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

Similar Threads

  1. Summary of records
    By csiro in forum Reports
    Replies: 4
    Last Post: 11-22-2019, 05:37 PM
  2. Replies: 10
    Last Post: 02-07-2018, 10:00 AM
  3. Replies: 4
    Last Post: 07-16-2015, 04:49 PM
  4. Replies: 1
    Last Post: 02-13-2015, 01:56 PM
  5. Replies: 6
    Last Post: 11-27-2014, 03:21 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