Results 1 to 14 of 14
  1. #1
    Numpty is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2019
    Posts
    8

    Query relationships creating extra data

    Hi guys. Finally given up being stubborn and now asking for help.
    I have a query set up which pulls together two tables:


    Jobs which have been completed, and workers times.
    Linked as one- many(multiple workers times to each job)
    And filtered to show only one customer(which has multiple jobs) at a time.

    The problem I have is, the job table! [job value] appears on every record, which, if used in a report, adds all the records.

    So, if a job_value is set at £50, and 3 sets of times, or 3 workers attend that job, the report calculates this as £150

    Any ideas how to have this not multiply the data?
    Or even to make the report footer calculation only look at the field in a subheader rather than each record.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Linked as one- many(multiple workers times to each job)
    This may not be what you actually have. Many workers to one job for sure, but can a worker not work on more than one job? If so, you have a many to many relationship between workers and jobs. That may not be your issue, though. If your report "groups by" job and the job value is calculated in the job footer, you may be OK. I'd advise you to consider if your current design supports many to many or just one to many as I'm saying. You might not have your tables designed correctly.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Numpty is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2019
    Posts
    8
    In this case, I am looking for output sorted by the job, so have given in one-many relationship, though over the entire database, yes, one worker can also contribute to many jobs.
    The main issue seems to be that the relationship simply causes the job detail to be listed in same record as each times record for that job.

    Can we put link in here?

    Http://www.gardenpeople.net/fileaccess/photo.pdf
    Shows the errors I get.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ummmm, after looking at the PDF, I think you might have some design problems and naming issues.
    You have spaces and special characters in names ("#" and "/").
    I would recommend you revisit your tables designs/relationships before continuing.
    Maybe post your dB here for analysis.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Aside from that, the pictures don't help much. Multiple records may or may not have just one field that's different, in which case that makes it not a true duplicate. Can't tell you whether to try DISTINCT or DISTINCT ROW predicates in your query, or if you need grouping by Job in the report, or if a totals query would help, or if you need to split off fields to eliminate the dupes... Any of those alone or in combination might help but would only be a band-aid approach because price divided by unit is a bad name (Price/Unit). Access may even try to divide what it thinks are variables in such cases - I've seen it happen before.

  7. #7
    Numpty is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2019
    Posts
    8
    Spaces, yes. Special characters no. Use vba alot, so spaces in field names is an easy workaround.
    The dB can't be posted, mainly because it holds customer data(not easily edited to hide info) , but also as it is over 100mb.(was 1.2gb at one point until got tidied).

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    # and / are considered special characters, so you must be meaning something else by that comment? Spaces require brackets, so why have them? No need to answer that as it's just a personal viewpoint, but I know it's a common one. I don't even use spaces in network paths because they become a pain in code.

  9. #9
    Numpty is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2019
    Posts
    8
    I see what you call special characters. Hash & slash are within the ascii set of valid characters for names in ms access which is why they were used to shorten field names . Not sql, though.

    @micron
    Totals query eliminates record data which is needed in report, this is distinct row query, I believe. Not sure how to change to distinct, or what effect that might have. Will have a look in morning.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hash & slash are within the ascii set of valid characters for names in ms access
    Chalk that up to just one more thing that you can do in Access that you shouldn't. Right up there with calculated table fields, multi-value fields, lookup fields in tables...

  11. #11
    Numpty is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2019
    Posts
    8
    Managed to get it on the report end by copying the job value field, and making the copy a running sum.

    Then just referencing the copied field.

    I know my dB has messy names and some very random processes to get a long calculation figure. All because it has been built together and added to over the last 20 years pretty much adhoc.
    On that note, will be starting new thread to figure out how best to change it a bit.

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I find all but the simplest report problems difficult to solve in this fashion because their design can be so complex and the information given is most often insufficient to convey complete understanding. From what I can tell, it hasn't even been revealed as to whether or not you have a grouping over job number. Sometimes it is enough to just sum over group, which is a built in report option IIRC. Anyway, you should close this one if your happy with the outcome (mark as solved).
    Thanks for posting the solution!

  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 know you said that the dB has been built/modified over the past 20 years, so this is just an FYI.

    RE: Naming objects (objects are field, table, form, query, report, module and macro names)

    Generally accepted "rules"
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


    The main reason for names with only letters and numbers is that most other RDBMSs do not allow spaces, punctuation or special characters in object names.
    So if your dB gets big enough or you want to move the BE (you DO have a split dB.... right??) to a more secure dB engine, you will have to use SQL Server, MySQL, Oracle, etc. which means that with spaces, punctuation and/or special characters, you will need to rewrite you dB.


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

  14. #14
    Numpty is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2019
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    I know you said that the dB has been built/modified over the past 20 years, so this is just an FYI.

    RE: Naming objects (objects are field, table, form, query, report, module and macro names)

    Generally accepted "rules"
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Good luck with your project.......
    Thanks, Steve.
    That is my plan, to make sql, & tidy up to minimise memory drag. and how did you guess I have a 3-way split dB (using vbcopy to update records between each segment).

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

Similar Threads

  1. Creating relationships in tables
    By Vibes in forum Reports
    Replies: 3
    Last Post: 06-11-2016, 01:06 AM
  2. Replies: 18
    Last Post: 06-19-2013, 10:58 AM
  3. Replies: 3
    Last Post: 04-27-2012, 08:34 AM
  4. Extra data
    By newtoAccess in forum Reports
    Replies: 14
    Last Post: 11-28-2010, 12:05 AM
  5. Creating Relationships and Queries
    By nacho in forum Database Design
    Replies: 4
    Last Post: 04-16-2010, 03:22 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