none
how to append a query with multi-value fields by specific field value

    Question

  • I need help with a query. I am trying to archive and entire table  and i want it to archive if the value of one of my fields is the valued i specify. but it keeps coming up with an error.
    here are some of the fields.

    TABLE= CONTACTS
    FIELDS= Name, address, next of kin, day started class, and student Status(multi-values are here)
    in my student status i have  values such as awaiting enrollment, in class, graduated. 
    i want my query to archive all the information on the student if the "student status"  is changed to graduated. 

    Please help 

    Monday, March 26, 2012 3:36 AM

Answers

  • Hi,

    to get all the graduated students, try this query:

    SELECT * FROM CONTACTS
    WHERE Status.Value = "Graduated"
    Note .Value after a field name. If it works for you, make a corresponding append query you want.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Marked as answer by Bruce Song Monday, April 02, 2012 9:03 AM
    Monday, March 26, 2012 5:43 AM
  • Johnny,
     
        For handling multi-value fields, two of my sample db's as mentioned below, might be of help in providing some working ideas:
     
        1 - Query_A2K10_MultiValueFields
        2 - Form_A2K10_MultiValueFields
     
        Sample 1 demonstrates query based approach to bulk appending / updating / make table actions involving multi-value  fields in Access 2010, without resorting to use of recordset or recordset2 objects, thus overcoming a known limitation associated with such fields.
     
        Three styles are covered as follows:
        (a) Bulk appending of selected records (having multi-value fields) from one table to another.
        (b) Bulk updating of multi-value field elements (addition or replacement of values) in destination table based upon values held in source table.
        (c) Make table action covering multi-value fields based upon selected records in source table.
     
        Sample 2 covers handling of multi-value fields (MVF) on access forms. Three aspects are demonstrated as follows:
     
        (a) Adding / Replacement of MVF value elements in target subform as per source subform.
        (b) Applying Form Filter on multi-value field as per reference multi-value field.
        (c) Sorting by multi-value field (bound column or displayed column as desired)
     
        Both samples (in accdb file format - developed on Access 2010) are available at Rogers Access Library. Link:
     
    Best wishes,
    A.D. Tejpal
    ------------
     
     
    ----- Original Message -----
    From: Johnnytuba
    Newsgroups: Msdn.en-US.accessdev
    Sent: Monday, March 26, 2012 09:06
    Subject: how to append a query with multi-value fields by specific field value
    I need help with a query. I am trying to archive and entire table  and i want it to archive if the value of one of my fields is the valued i specify. but it keeps coming up with an error.
    here are some of the fields.
     
    TABLE= CONTACTS
    FIELDS= Name, address, next of kin, day started class, and student Status(multi-values are here)
    in my student status i have  values such as awaiting enrollment, in class, graduated. 
    i want my query to archive all the information on the student if the "student status"  is changed to graduated. 
     
    Please help 

    A.D. Tejpal
    • Marked as answer by Bruce Song Monday, April 02, 2012 9:03 AM
    Monday, March 26, 2012 12:09 PM

All replies

  • What is the exact error you receive and can you post the SQL you have so far?


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Monday, March 26, 2012 4:08 AM
  • Hi,

    to get all the graduated students, try this query:

    SELECT * FROM CONTACTS
    WHERE Status.Value = "Graduated"
    Note .Value after a field name. If it works for you, make a corresponding append query you want.

    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru

    • Marked as answer by Bruce Song Monday, April 02, 2012 9:03 AM
    Monday, March 26, 2012 5:43 AM
  • What type of database are you trying to Query (MDB or some other database)?

    Are you typing in your query manually or using the Query Wizard?

    Is you query in VBA macro?  If so what is your connection statement


    jdweng

    Monday, March 26, 2012 9:07 AM
  • Johnny,
     
        For handling multi-value fields, two of my sample db's as mentioned below, might be of help in providing some working ideas:
     
        1 - Query_A2K10_MultiValueFields
        2 - Form_A2K10_MultiValueFields
     
        Sample 1 demonstrates query based approach to bulk appending / updating / make table actions involving multi-value  fields in Access 2010, without resorting to use of recordset or recordset2 objects, thus overcoming a known limitation associated with such fields.
     
        Three styles are covered as follows:
        (a) Bulk appending of selected records (having multi-value fields) from one table to another.
        (b) Bulk updating of multi-value field elements (addition or replacement of values) in destination table based upon values held in source table.
        (c) Make table action covering multi-value fields based upon selected records in source table.
     
        Sample 2 covers handling of multi-value fields (MVF) on access forms. Three aspects are demonstrated as follows:
     
        (a) Adding / Replacement of MVF value elements in target subform as per source subform.
        (b) Applying Form Filter on multi-value field as per reference multi-value field.
        (c) Sorting by multi-value field (bound column or displayed column as desired)
     
        Both samples (in accdb file format - developed on Access 2010) are available at Rogers Access Library. Link:
     
    Best wishes,
    A.D. Tejpal
    ------------
     
     
    ----- Original Message -----
    From: Johnnytuba
    Newsgroups: Msdn.en-US.accessdev
    Sent: Monday, March 26, 2012 09:06
    Subject: how to append a query with multi-value fields by specific field value
    I need help with a query. I am trying to archive and entire table  and i want it to archive if the value of one of my fields is the valued i specify. but it keeps coming up with an error.
    here are some of the fields.
     
    TABLE= CONTACTS
    FIELDS= Name, address, next of kin, day started class, and student Status(multi-values are here)
    in my student status i have  values such as awaiting enrollment, in class, graduated. 
    i want my query to archive all the information on the student if the "student status"  is changed to graduated. 
     
    Please help 

    A.D. Tejpal
    • Marked as answer by Bruce Song Monday, April 02, 2012 9:03 AM
    Monday, March 26, 2012 12:09 PM