Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I need the three sets to still be 3 sets returned to the user. The big difference is I don't want to use the seperate 3 tables, just the master instead, and also it needs to be multiple returns(if exist) to the three sets of account controls.

  2. #17
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I will review post #4 and give it a whirl. What is the best method to return the query results to 3 seperate controls? Combo boxes or a datasheet return? Or something else?

  3. #18
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I am attempting this query with error: "Syntax error in query. incomplete query clause."

    SELECT DOL, [last name], [first name], 1 AS Source, account1 AS AccountNum FROM master
    UNION SELECT DOL, [last name], [first name], 2, account2 FROM master
    UNION SELECT DOL, [last name], [first name], 3, account3 FROM master;

    What are the 1, 2, and 3 for? I am missing something...

  4. #19
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I have my query working. Now I am trying to figure out how to do a lookup to return the results to the users form. What we do is in the form have them enter the account number in the box. When they press tab and that account number field loses focus it automatically does a DLookup of the account number for all three account fields. I was thinking of using an ELookup instead but can't quite figure that one out. Any suggestions?

    PS: I am truly a beginner at this so i'm sorry for my ignorance on some of these matters...

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    The 1, 2, 3, identify the source field (account1, account2, account3). Can use this as a filter parameter in the lookup if you want.

    There is no ELookup intrinsic domain aggregate function. There is DLookup.
    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
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    Ok, thank you for all your help. I have my form working now!

    So now I need to work on the table. I added the new column [date] to the table. I need to match the accountnumber AND lastname fields of my new UNION query you helped me build and populate the [date] field of those matches to the new [date] field i created in the table.

    So:

    Table with new date field:

    Table1
    ...ID
    ...box
    ...lastname
    ...firstname
    ...accountnumber
    ...user
    ...datetime
    ...date(this is the new field to be populated)

    UNIONquery
    ...accountnumber
    ...date
    ...lastname
    ...firstname

    I need to match the accountnumber AND lastname from the table and the UNION query and populate the [date] field in the table from the matches. How can I do this?

    Thanks,
    Tom

  7. #22
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    Create query to join the two tables on the accountnumber and lastname and firstname fields. Click UPDATE on the ribbon, set update criteria under the new field to the old date field. Click run on ribbon.

    Access Help has guidelines on using the query builder.
    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.

  8. #23
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I did an update and it's updating the wrong way. It's taking the empty field from the new column I created and copying those empty [date] fields over to the source database.

    So I reversed the UPDATE [table1] statement and its STILL updating the wrong way??

    What am I doing wrong?

    At first I had it like this:
    UPDATE DestinationTable INNER JOIN [SourceTable]
    ON (SourceTable.[Indv Lst Nm] = [DestinationTable].custnamel) AND (SourceTable.AcctNum = [DestinationTable].account)
    SET SourceTable.date = [DestinationTable].[date]
    WHERE ((([DestinationTable].[date])<>[SourceTable].[date]));

    So I changed it to:
    UPDATE SourceTable INNER JOIN [DestinationTable]
    ON (SourceTable.[Indv Lst Nm] = [DestinationTable].custnamel) AND (SourceTable.AcctNum = [DestinationTable].account)
    SET SourceTable.date = [DestinationTable].[date]
    WHERE ((([DestinationTable].[date])<>[SourceTable].[date]));

    What is my malfunction?

  9. #24
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    The SET clause is backwards.
    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.

  10. #25
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I have this:

    UPDATE SourceTable INNER JOIN [DestinationTable]
    ON (SourceTable.[Indv Lst Nm] = [DestinationTable].custnamel) AND (SourceTable.AcctNum = [DestinationTable].account)
    SET [DestinationTable].[date] = SourceTable.date
    WHERE ((([DestinationTable].[date])<>[SourceTable].[date]));

    and when I hit the "View" button to see the datasheet view before I actualy run the query, all the [date] fields are empty. Do I still have something wrong? I tried removing the WHERE statement all together and it still didn't work.

  11. #26
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    If the field to be updated is currently empty, I would expect datasheet view of UPDATE to be blank for that field because it hasn't been updated yet. It's an action sql.
    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.

  12. #27
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    When I hit Run, it says it's going to update 0 rows...

    Any ideas?

  13. #28
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    No. AFAIK, that should work. At this point would have to work with your database to analysis issue. I won't have time for that for a week.
    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.

  14. #29
    twildt is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    56
    I figured it out. I took out the WHERE statement again and it worked. I guess because the initial fields were blank it couldn't determine they were <> not alike.

    You have single handedly helped me solve my biggest issue. I have to say, it was tough for me not knowing some of these features(I know, I need to take a friggin class on Access), but you really taught me a lot. I have been doing some serious reading and research on these topics your helping me with and I am impressed I was able to figure this out so quickly.

    Slow for you I'm sure. Nonetheless I couldn't have done it without you.

    Thanks again,
    Tom

  15. #30
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,770
    Ooops! I should of recognized that issue with Null.

    Perhaps this:

    WHERE IsNull([DestinationTable].[date])

    However, if all the records needed to be updated then no reason to filter.
    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.

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

Similar Threads

  1. I am stumped...adding field to existing table
    By tanyalee123 in forum Queries
    Replies: 6
    Last Post: 12-09-2013, 06:04 PM
  2. Replies: 3
    Last Post: 07-30-2013, 12:11 PM
  3. Replies: 1
    Last Post: 07-22-2013, 10:17 PM
  4. Replies: 6
    Last Post: 06-14-2013, 05:43 PM
  5. Programmatically add a field to an existing table
    By lpdds in forum Database Design
    Replies: 2
    Last Post: 11-10-2009, 12:45 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