Results 1 to 12 of 12
  1. #1
    coldspring is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    6

    Question Merging Multiple Records Into One value for a specific field

    Hi, I am new to Access and am trying to build a database for my work while I am learning it. I have built the basic tables and forms and now need to find a solution to generate a query based on a specific organization rule for the records. To better illustrate my problem, I made a hypothetical database (in the attachmentTest database.zip).

    This is a very simple order database composed of three tables: tblOrder, tblOrderDetail, and tblItem (see relationship.jpg). The tblOrder contains two fields, OrderID and CustomerName. The tblItem contains ItemID, ItemName, ItemPrice, as well as a ItemCategory field. Each item in tblItem belongs to a category under the ItemCategory field. For example, apple, orange, banana all belong to Fruit, and beef, pork belong to Meat (see Item table.jpg). The tblOrder and tblItem have a many-to-many relationship joined by tblOrderDetail.

    Click image for larger version. 

Name:	relationship.jpg 
Views:	26 
Size:	67.5 KB 
ID:	14023Click image for larger version. 

Name:	Item table.jpg 
Views:	26 
Size:	74.9 KB 
ID:	14024Click image for larger version. 

Name:	Order form.jpg 
Views:	26 
Size:	85.1 KB 
ID:	14025
    (Please note that the [ItemID] field in tblOrderDetail form stores the numeric value from the primary key field [ItemID] in tblItem, but shows the text of the [ItemCategory] field in tblItem through a lookup relationship. The [ItemID] foreign key value itself is hidden in tblOrderDetail, however.)

    To input order information, I have created a form called frmOrder with an embedded subform called sfmOrderDetail (see OrderForm.jpg). The sfmOrderDetail subform is based on a query qryOrderDetail that gathers information from both tblOrderDetail and tblItem. In this organization, each order would have several corresponding records of Order Detail, with each having one item. Now my task is to generate a table (or a query that can generate such table) with each order having just one record. In this record, the different item names belonging to the same category are concatenated into one string and become one single value in a specific category field. Take OrderID 2 for example, Susan ordered apple in the fruit category, beef and pork in the meat category, and nothing in the drink category. Therefore I want to put "apple" in a [Fruit] field, "beef/pork" in a [Meat] field, and an empty value in a [Drink] field for a record corresponding to OrderID 2. I thought I would need to use an Append query to call item names and append it to the specific category fields, but that would need to first determine which category field is the target field based on the ItemCategory value. Also, items in the same category will need to be concatenated. How should I do that? Note that in this particular example, the quantity of items for each order is irrelevant.



    I know what I am trying to do with this hypothetical database doesn't make much sense. I am just using it to explain what I want to achieve. In my real database, for each order, there wouldn't be more than two item names under the same category. I thought knowing this may make this task slightly less complicated. I suspect some SQL statements are necessary for achieving my goal, but I really have no idea how to do it.

    I guess there are a large number of experts here who can easily solve this problem. Could someone please give me some help? I will need to get this database ready in a couple of days. I looked through some previous posts but couldn't find a relevant answer, although I could have missed them. Any help is much appreciated.

    Regards,

    Chun

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    "The [ItemID] foreign key value itself is hidden in tblOrderDetail" - if this means you have set lookup in table, advise not to, review http://access.mvps.org/access/lookupfields.htm

    What you want usually requires VBA code. Review http://allenbrowne.com/func-concat.html

    I do not recommend you save these concatenated values to a field in table. Why would there never be more than 2 items for a category?
    Last edited by June7; 10-15-2013 at 03:47 AM.
    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
    coldspring is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    6
    Quote Originally Posted by June7 View Post
    "The [ItemID] foreign key value itself is hidden in tblOrderDetail" - if this means you have set lookup in table, advise not to, review http://access.mvps.org/access/lookupfields.html

    What you want usually requires VBA code. Review http://allenbrowne.com/func-concat.html

    I do not recommend you save these concatenated values to a field in table. Why would there never be more than 2 items for a category?
    I can't see the link somehow. How is this lookup relationship different from regular inner join relationship?

    The concatenated value doesn't have to be in table, as long as I can get it through a query. I will need to use it for creating a report with a specific format. In my real database (a fruitfly database for biology research), each category is actually a specific pair of chromosomes which I will need to document their genotypes (like item names). Because each pair of chromosomes only contain two chromosomes, for which I either document the genotypes of one, both, or none of the pair, I don't need to concatenate more than two records. I created the order example so that my problem can be easily understood.

    I know nothing about VBA code. Can someone help me on this?

    Thanks a lot!

    Chun

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why would you use example that has no connection whatsoever to the situation? Examples true to nature of the database and the issue usually best.

    I just clicked the link and it worked for me.

    Options:

    1. the code in referenced link

    2. two fields in table, not according to strict data normalization but maybe best for your situation

    3. the two records for each pair are numbered 1 and 2 in a field for that purpose then 2 (maybe 3) queries (or a nested query) can produce the concatenated result.
    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
    coldspring is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    6
    Hi June7,

    Thank you for your reply. First please excuse me for my ignorance in Access. I am just beginning to learn Access with my efforts in building my database. So from time to time, I may have trouble to fully grasp your suggestions. For the options you mentioned, I kind of understand your intentions but really have no idea how to take on.

    For my hypothetical order database, the query I have in mind is something like in this image:
    Click image for larger version. 

Name:	final query.jpg 
Views:	21 
Size:	58.2 KB 
ID:	14039
    As I don't have the query actually made, I cheated by typing in all the values in a table. As you can see, I would like each order to take only one row. Now different categories (Drink, Fruit, Meat) become individual fields in the query. For each order, the items within each category are merged into one value (with names separated by "/").

    My real database Stock.zip contains a lot of technical terms which I thought may make people confused. That is why I made up the hypothetical database. I have simplified my database and attached it here. As you can see, it is very similar to the hypothetical database in structure. You can consider tblStocks as tblOrder; tblChromosome as tblItem; and tblStockChrosomsome as tblOderDetail. In the tblChromosome, [Chromosome] would be the [Category] equivalent, [Genotype] would be [ItemName]. Please take a look if it makes any sense to you.

    I still have trouble with your lookup in table link, but I can open the VBA code link. I will try to study the VBA page. But because I had zero knowledge in VBA, it will probably take me some time to understand and tailor the function to my need.

    In any case, I appreciate your help and input very much!

    Chun

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your lookups in tables are not referencing lookup tables for the RowSource, they are value lists. There is no alias involved, this is WYSIWYG. Modifying value list involves modifying the field property. If the RowSource were based on a table, then adding a new value would just mean adding a new record in the associated lookup table. If you are the only user of this db and you don't mind editing the properties as needed, or will never have new values (StockType is a long list now), then this is fine. As long as the RowSource does not involve alias then setting the lookup in table is not an issue, because this isn't really doing a 'lookup'.

    However, why is StockType set to allow multiple values? Maybe I should have listed multi-value as option 4 but I NEVER use multi-value field and do not recommend.
    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
    coldspring is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    6
    Hi June7,

    I think I start to understand the lookup table issue you are talking about in the beginning, but please correct me if I am wrong.

    Are you referring to the [Chromosome] field in tblChromosome being a lookup field instead of referencing to a separate table? It seems like, although I still don't understand why, using lookup fields is not a sound practice. I thought since Microsoft put this feature in Access, it must be useful in certain ways. When I built my database, I found it was easier to use the lookup field than creating another table. I could change it to a table, though, if it offers advantages. Regarding the [StockType] field, because of the nature of each stock, it could belong to more than one type. I could of course build another StockType table, which would be in a many-to-many relationship with tblStock.

    Would you mind to give me some detailed suggestions on how to make the concatenation happen? Ideally, it is best to merge the two records into one value with a "/" in between.

    Thanks much!

    Chun

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The advantage of using a table as the source for a combobox list is ease of adding new items. What you have now requires a design change, using a table is just adding a new record.

    Sorry, you meant the link about lookups was not working - here it is again http://access.mvps.org/access/lookupfields.htm

    I have already given you ideas for methods of 'merging' the two records. Which one do you want to use? Options 2 and 4 require modifying table into a less normalized structure and editing records, option 2 is adding and populating another field. Option 1, Allen Browne's function, works with your current structure.
    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.

  9. #9
    coldspring is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    6
    Hi June7,

    I got it now. Is there an easy way for me to convert the lookup field into values linked to a reference table? I am thinking to use an update query. But I may have trouble dealing with the multi-value lookup field. Do you have suggestions.

    I think your option 1 makes a lot of sense and may be more straightforward than option 3. I would not use options 2 and 4 if they are not good to the database structure. I will spend some time to study Allen Browne's function.

    Thank you again!

    Chun

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As said, I would not do multi-value field (option 4) but I am not averse to the two-column approach (option 2). I do have tables that are not fully normalized as that suits my requirements. I feel it is a balancing act between normalization and ease of data entry/output. However, that would require some effort to modify the db. The only option that does not require modifying structure is option 1.

    I still don't understand why StockType field is set to allow multiple values.

    An UPDATE action would be used to change the values if you want to save an ID instead of the text descriptor (this would be a 'lookup' with 'alias'). Since these descriptors are short, consider continuing to save them. An ID would be better if the descriptors were long and/or there was more info in the lookup table that needed to be viewed. Saving the descriptors instead of an ID eliminates the complications of multi-column combobox as well as having to join tables in queries.

    The present ValueList settings as RowSource in comboboxes will work. It's a matter of convenience if you want to use tables as RowSource. If you see a need to regularly add new values, then adding a record to table is easier than having to edit the ValueList in table.

    Keep in mind, if you build a form with controls based on these fields, changing any property in table will not automatically change the existing form controls. You must opt for this during the edit. The reverse is also true. Editing a control on form will not alter the table (and no option to do so). If you are building this db for other users, should build forms and reports as interface. Users should not work directly with tables and queries. I never do these settings in tables, I just build forms and reports and configure the controls.

    I just took a look at the forms. The query used as subform RecordSource should be a RIGHT JOIN instead of INNER.

    What you are doing for the ChromosomeID combobox is what I have described for a multi-column combobox with a table/query as the RowSource. The Chromosome, Genotype, and ChromosomeType controls should be locked textboxes and not allow edits. This subform should be used to select an existing record from tblChromosome and save via the combobox the ID of that record into tblStockChromosome. The related info can be viewed but not edited on this form nor saved into tblStockChromosome.
    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.

  11. #11
    coldspring is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Posts
    6
    Quote Originally Posted by June7 View Post
    I still don't understand why StockType field is set to allow multiple values.
    Sorry for the confusion. The StockType was set up to reflect the major components in a stock and how the stock may be used for genetic crosses. One stock can serve multiple functions, hence the mutli-value nature. While I am playing with my complete database, I found that once I set up a field as multi-value lookup field, I couldn't convert it back to single-value field, even though all records in the field of the entire table contain only single values. Is there a way to convert it without starting over the table from scratch?

    An UPDATE action would be used to change the values if you want to save an ID instead of the text descriptor (this would be a 'lookup' with 'alias'). Since these descriptors are short, consider continuing to save them. An ID would be better if the descriptors were long and/or there was more info in the lookup table that needed to be viewed. Saving the descriptors instead of an ID eliminates the complications of multi-column combobox as well as having to join tables in queries.

    The present ValueList settings as RowSource in comboboxes will work. It's a matter of convenience if you want to use tables as RowSource. If you see a need to regularly add new values, then adding a record to table is easier than having to edit the ValueList in table.

    Keep in mind, if you build a form with controls based on these fields, changing any property in table will not automatically change the existing form controls. You must opt for this during the edit. The reverse is also true. Editing a control on form will not alter the table (and no option to do so). If you are building this db for other users, should build forms and reports as interface. Users should not work directly with tables and queries. I never do these settings in tables, I just build forms and reports and configure the controls.
    I am glad to know my current structure isn't a too big problem. But I realize that you are giving me a lot of good lessons on the fundamentals of database building. This is my first time using, let alone making a Access database. Thank you for all your sound advices. I am building this database to manage the fruitfly strains in my lab. In the foreseeable future, I will be the only user handling this database, therefore I don't particularly worry about others accidentally messing up the database. But I may in one day hand this database to someone else. For that, I know I have a lot of work to do on the UI.

    I just took a look at the forms. The query used as subform RecordSource should be a RIGHT JOIN instead of INNER.
    Thanks for pointing that out. I always thought that relationship should be set up at table level. Now I learned that the relationship in a query can be different from the relationship among tables. Do you think this relationship should be changed at the table level too?

    What you are doing for the ChromosomeID combobox is what I have described for a multi-column combobox with a table/query as the RowSource. The Chromosome, Genotype, and ChromosomeType controls should be locked textboxes and not allow edits. This subform should be used to select an existing record from tblChromosome and save via the combobox the ID of that record into tblStockChromosome. The related info can be viewed but not edited on this form nor saved into tblStockChromosome.
    Right now I am the only user of this db. I set it up this way because I can have a convenient way of creating new entries for the tblChromosome within the same Stock Entry form. In fact, in most cases of the real life, entering a new stock involves creating new tblStockChromosome records. I understand that an alternative way is to lock the fields in the subform and when in need for entering new chromosome data, to open a Entry form for tblChromosome and add the value. But I felt that it is cumbersome and couldn't find a better idea. Any idea from you will be welcome.

    Thank you!

    Chun

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Getting rid of the multi-value field will require creating a new single-value field and populating it then deleting the old field. The new field can be populated with an update query that draws from the multi-value field but really need to understand how to work with multi-value fields in query. If you need to develop that understanding, review http://office.microsoft.com/en-us/ac...010149297.aspx

    Setting relationships can be useful when building queries. Pull the related tables into query designer and the join type will be automatically set. Make the relationship the type that would be normal for the tables so not always 'fixing' the query.

    Whichever is more cumbersome, editing the RowSource property or opening form to add new record, is determination subject to: user experience, intentions for the db, frequency of list modifications, and number of list items. If you want to build for other users who shouldn't be mucking with design, definitely use a table. In the long run, I think you will find the table approach more convenient for yourself even. Either approach can take advantage of the combobox NotInList event to modify the ValueList or add new record.
    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.

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

Similar Threads

  1. Replies: 5
    Last Post: 05-22-2013, 12:38 PM
  2. Replies: 1
    Last Post: 04-04-2013, 11:59 AM
  3. Replies: 2
    Last Post: 02-28-2012, 12:43 PM
  4. Replies: 2
    Last Post: 02-22-2012, 02:36 AM
  5. Combining / Merging Records
    By alpinegroove in forum Access
    Replies: 8
    Last Post: 01-27-2011, 09:43 AM

Tags for this Thread

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