Results 1 to 7 of 7
  1. #1
    canyon289 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4

    Data Analysis Direction

    Hi!
    I'm completely new to Access and was wondering if I could get some guidance on a simple project I'm working on.

    I'm working on an organization project where I need to find all jobs with like information. Here is an example dataset.

    Job Number Item1 Item 2 Item 3 Item 4
    1 a b c
    2 a c
    3 d a
    4 a b c d
    5 c b


    Copied from third post. Asked question badly the first time.


    In mathematical terms if I pick a record I want it to define a set from all the items. e.g. If I select record 1 I want the set to be {a,b,c}. Then I would like the process to return all records with all or some of the item values.

    Any direction would be extremely helpful! I'm glad to have found this forum and thanks in advance.
    Last edited by canyon289; 03-04-2012 at 05:28 PM. Reason: Badly/Wrongly worded

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your data structure is not normalized.

    You want to select records based on multiple values in another record? Never seen anything like this but I guess could be done, just not seeing a process right away.

    Don't really understand the criteria. With record 1 as the criteria, why is record 3 not selected? Record 3 has some of the given values along with an extra like record 4 and like records 2 and 5 a matching value is not in the same column. You want to return records where any ONE of the fields is a match?

    If you 'select' job 1 isn't job 1 returned along with 2, 4, 5?

    Build a form that has the table as RecordSource. Build a query that refers to textboxes of form as parameters. Use the OR operator in the query design for the parameters. Query would look like:

    SELECT JobNumber, Item1, Item2, Item3, Item4
    FROM Table1
    WHERE (((Item1)=[Forms]![Form1]![Item1])) OR (((Item2)=[Forms]![Form1]![Item2])) OR (((Item3)=[Forms]![Form1]![Item3])) OR (((Item4)=[Forms]![Form1]![Item4]));
    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.

  3. #3
    canyon289 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    thanks for the quick reply!
    You're right, I'm confusing myself. If I select record 1, the process should return 1,2,5. If I select 2 it should select nothing else. If I select 4, it should return 1,2,3,4,5.
    In mathematical terms if I pick a record I want it to define a set from all the items. e.g. If I select record 1 I want the set to be {a,b,c}. Then I would like the process to return all records with all or some of the item values.

    I apologize for asking the question terribly/completely wrong.

    Nonetheless thank you SOO much for a response and giving me something to work with.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Still confused.

    Why should selection of record 1 not also return record 4?

    Why should selection of record 2 not return 1, 2, 4?

    Why should selection of record 4 return 3?

    The only record selection I can see that would not return more than itself is record 3. And record 3 would not be included if other records selected.

    Assuming values must be matched in the same column. If the values can be matched in any column, that requires a different approach from my suggestion.

    Verify the logic and I can provide alternate suggestion.
    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.

  5. #5
    canyon289 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    Record 1 contains the set of values {a,b,c}. You can create record 2 and 5 with these values {a,c} and {c,b}. You can't create record 4 because 4 contains "d" which isn't in the set which defines record 1.

    Records 2,3,5 should not return any other records other than themselves.

    Record 4 should return all values, because you can create every record with the values "a", "b", "c" and "d"

    Does that make a little more sense? Thanks for asking for clarification and I hope I explained it better this time.

    Edit
    Yes the values should be "column independent" That's where I'm having the most trouble with setting up this query. All the values in a record need to be treated a set without regard to which column it's located in.

    Again thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Okay, little more complicated but maybe this is it:

    Is your data actually single character in each field? Have a textbox on form that concatenates all 4 fields as a single string.

    Then the search query is:

    SELECT JobNumber, Item1, Item2, Item3, Item4
    FROM Table1
    WHERE (((IIf(Not IsNull([Item1]),IIf(InStr([Forms]![Form1]![Items],[Item1])>0,"Y","N"),Null) & IIf(Not IsNull([Item2]),IIf(InStr([Forms]![Form1]![Items],[Item2])>0,"Y","N"),Null) & IIf(Not IsNull([Item3]),IIf(InStr([Forms]![Form1]![Items],[Item3])>0,"Y","N"),Null) & IIf(Not IsNull([Item4]),IIf(InStr([Forms]![Form1]![Items],[Item4])>0,"Y","N"),Null)) Not Like "*N*"));
    Last edited by June7; 03-04-2012 at 11:48 PM.
    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
    canyon289 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    Okay, little more complicated but maybe this is it:

    Is your data actually single character in each field? Have a textbox on form that concatenates all 4 fields as a single string.

    Then the search query is:

    SELECT JobNumber, Item1, Item2, Item3, Item4
    FROM Table1
    WHERE (((IIf(Not IsNull([Item1]),IIf(InStr([Forms]![Form1]![Items],[Item1])>0,"Y","N"),Null) & IIf(Not IsNull([Item2]),IIf(InStr([Forms]![Form1]![Items],[Item2])>0,"Y","N"),Null) & IIf(Not IsNull([Item3]),IIf(InStr([Forms]![Form1]![Items],[Item3])>0,"Y","N"),Null) & IIf(Not IsNull([Item4]),IIf(InStr([Forms]![Form1]![Items],[Item4])>0,"Y","N"),Null)) Not Like "*N*"));
    Thank you again!

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

Similar Threads

  1. New and need some help and Direction!
    By OlneyFD in forum Access
    Replies: 0
    Last Post: 12-02-2011, 07:08 PM
  2. Table Data Analysis
    By bdf48 in forum Programming
    Replies: 1
    Last Post: 12-01-2011, 02:21 PM
  3. SAS(Statistical Analysis System) to SQL
    By Rixxe in forum Programming
    Replies: 4
    Last Post: 10-14-2010, 08:47 AM
  4. vba code for regression analysis P value output
    By Frangipani in forum Programming
    Replies: 0
    Last Post: 02-04-2009, 06:26 AM
  5. Need Help with Queries-Trade Analysis
    By nybanshee in forum Queries
    Replies: 0
    Last Post: 03-08-2008, 11:50 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