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.
(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