Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just did a test with empty strings in text field with numeric type data and find I was wrong. The numbers will return as Max() value so empty string is irrelevant. Sort order for text field appears to be: Null, non-printing characters, punctuation/special characters, digits, letters - almost same order as ASCII codes.

    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.

  2. #17
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Micron, I just had an extra field added to my data and put in a third join statement and get syntax error in the From Clause.
    Code:
    SELECT hikerdood.Doc, hikerdood.Commit, hikerdood_1.Obligation, hikerdood_2.Expense, hikerdood_3.Liquidation
    FROM (hikerdood INNER JOIN hikerdood AS hikerdood_1 ON hikerdood.Doc = hikerdood_1.Doc) INNER JOIN hikerdood AS hikerdood_2 ON hikerdood_1.Doc = hikerdood_2.Doc, INNER JOIN hikerdood AS hikerdood_3 ON hikerdood_2.Doc = hikerdood_3.Doc
    WHERE (((hikerdood.Commit)<>"0") AND ((hikerdood_1.Obligation)<>"0") AND ((hikerdood_2.Expense)<>"0") AND ((hikerdood_3.liquidation)<>"0")));

    Not all that familiar on separating 3 joins. Appreciate your help

    Regards

    Gene

  3. #18
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Note that with 3 tables, 2 are grouped (1 & 2 ), the 3rd isn't - thus (1 & 2) & 3.
    With 4 tables you need to group first 2 as a inner/nested group, then the 3rd is the outer grouping of that group and the last is not - ((1 & 2) & 3) & 4. If you had dragged the table into the existing query, made the join and set the criteria, Access would have done this automatically. You also have 9 left and 7 right parentheses - not going to work for that reason either. If you get stuck I can post the sql but I figured you'd like to try going to design view and added the table manually. I also take it that your fields are text, not numbers, because you have "0" not 0.

    I'm beginning to wonder about the tables or whatever it is that's requiring 4 versions of the same table in the same query. Pretty unusual I'd say.

  4. #19
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44

    Cross Tab

    Quote Originally Posted by Micron View Post
    Note that with 3 tables, 2 are grouped (1 & 2 ), the 3rd isn't - thus (1 & 2) & 3.
    With 4 tables you need to group first 2 as a inner/nested group, then the 3rd is the outer grouping of that group and the last is not - ((1 & 2) & 3) & 4. If you had dragged the table into the existing query, made the join and set the criteria, Access would have done this automatically. You also have 9 left and 7 right parentheses - not going to work for that reason either. If you get stuck I can post the sql but I figured you'd like to try going to design view and added the table manually. I also take it that your fields are text, not numbers, because you have "0" not 0.

    I'm beginning to wonder about the tables or whatever it is that's requiring 4 versions of the same table in the same query. Pretty unusual I'd say.
    Thanks for the help. I agree but we switched over to a different DB and what use to be one record for a particular funding document # that posted the values such as Commitments, Obligations, Expense and Liquidation as one record now come down as four separate records under the same document #. What use to be as an example 3000 records will eventually turn out to be 12000 records.

    Again thanks for your help and knowledge.

    Regards

    Gene

  5. #20
    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 #10? Same could be done with 4 records.
    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.

  6. #21
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44

    Reply

    Quote Originally Posted by June7 View Post
    Presuming each Doc will have no more than 3 records and only 1 of the 3 amount fields of each record has numeric data, an aggregate query should compress the records.

    SELECT Doc, Max(Commit) AS DocCom, Max(Obligation) AS DocObl, Max(Expense) AS DocExp FROM Tablename GROUP BY Doc;

    However, there cannot be empty string in fields because any string value will sort after numbers and return as Max(). So do a Replace() operation to make sure there are no empty strings in any fields. Replace with Null (or 0). Null sorts before anything else.

    I NEVER allow empty strings in fields.
    I gave it a try and it works perfectly.

    Thank you

  7. #22
    hikerdood is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    44
    Micron, This works fine. Again thanks for your time and also to June7 in post #10. Learned a lot. Great site and appreciate your dedication to helping spread your knowledge.

    SELECT hikerdood.Doc, hikerdood.Commit, hikerdood_1.Obligation, hikerdood_2.Expense, hikerdood_3.Liq
    FROM ((hikerdood INNER JOIN hikerdood AS hikerdood_1 ON hikerdood.Doc = hikerdood_1.Doc) INNER JOIN hikerdood AS hikerdood_2 ON hikerdood_1.Doc = hikerdood_2.Doc) INNER JOIN hikerdood AS hikerdood_3 ON hikerdood_2.Doc = hikerdood_3.Doc
    WHERE (((hikerdood.Commit)<>0) AND ((hikerdood_1.Obligation)<>0) AND ((hikerdood_2.Expense)<>0) AND ((hikerdood_3.Liq)<>0));

  8. #23
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome; glad to have been able to help somewhat. I approached this as though the data was numeric, but after reviewing to see if you ever had revealed that, I don't see that it ever was stated. However, data you posted seems to be left justified, which usually indicates data is text. Good luck going forward.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  2. Cross Tab Query
    By Tammy in forum Queries
    Replies: 4
    Last Post: 05-29-2015, 09:58 AM
  3. Cross Tab Query
    By vishal09 in forum Access
    Replies: 3
    Last Post: 04-04-2015, 10:52 AM
  4. Cross Tab Query
    By RycherX in forum Queries
    Replies: 1
    Last Post: 04-26-2010, 12:19 PM
  5. Cross Tab Query
    By nengster in forum Queries
    Replies: 0
    Last Post: 02-18-2009, 07:12 PM

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