Results 1 to 2 of 2
  1. #1
    Franuzz is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2011
    Posts
    1

    Query using the same primary keys.

    Hello, first post here!
    I tried searching around the forum before posting this.
    Basically, I got a new job and inherited a database that is all over the place. They are using Access 2003(upgrading to 2007 is coming soon).

    I need a query that will combine multiple tables that have the same primary key.
    Some of the tables and some fields.
    Billing: jobID, Month, Hours, BillableRate (created a query to multiply these and sum per month)
    Jobs:jobID, month, client,
    Expenses: jobID, month, expenseAmt,

    Basically I need the new query to columns with Client,JobID, Month, Bill (from the query where I multiplied BillableRate and Hours), expense, and a few others but these will do as a starting point. A lot of these tables have different dates associated with them, so I would have to return a 0 value if there is no expense associated with a jobID for the same month. So if jobID and month are the same, return a value for expense, Bill, and other things.

    I tried many things, fiddling with dsum, and after many hours, come up empty. Is there something I'm doing wrong or overlooking?



    Thank you in advance!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    If there are multiple billing records with the same jobID in the Billing table (since you are summing them), that would imply that the JobID is a foreign key not a primary key. The problem comes in with the month field. First, the word month is a reserved word in Access, so it should not be used as a field name. Now for those jobs that span multiple years how do you know if the record with a particular month is for this year, last year of 5 years ago? You mentioned you had date fields, but you did not say in what tables. Also, I do not see the purpose of the month field in the job table.

    In terms of getting the data you want, I would probably suggest that you create a new query that has the job table and your summing query and do a left join between the job table and the query using both the jobID and month fields. But, I don't know if that will work because of the year issue I mentioned above.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-22-2010, 02:25 PM
  2. Multiple primary keys
    By elektrisk in forum Access
    Replies: 5
    Last Post: 02-11-2010, 04:39 PM
  3. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM
  4. Primary and Secondary Keys...
    By LittleOleMeDesigns in forum Database Design
    Replies: 5
    Last Post: 07-24-2009, 11: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