Find and replace full records (1 Viewer)

[jnolfo]

I have looked for a similar question, but could not find one like it. I am not even sure if this is the right place to put this question, but here I go.

I have a table of sales information. Part of my products are kits that are a combination of individual products. (Think of it as a value meal at a fast food restaurant. You can order a hamburger, a soda, and a small french fry separately. But if you get the Value Meal #1 you get all three at a discounted price.)

What I would like to do is make a new table/query that extracts out the kits out of the original sales table and replaces the kit record with records that show the components.

I have attached a file to show as an example of how the records current look and how I would like it to look.

 

[CJ_London]

assuming your data is normalised, just include the table which summarises the products per kit and join it to the kit table in your query

 

[jnolfo]

My data is normalized And I have linked the sales table to my Kit Component table. I guess, I am trying to figure out what to do next to make sure all of the customer info flows and the right values come out for multiple kits.

(I hope my example spreadsheet made sense)

 

[CJ_London]

not sure if you are still asking a question, but you'll need to provide your table structure and explain the relationships for a more focused response

 

[jnolfo]

Yes, I am still asking a question.

I have attached a new file:
Lines 1-9 shows the current transaction table (What it is)
Lines 12-25 show the new transaction table (What I want it to look like)
Lines 33-40 show the Kit Component table.

What I would like to do is figure out a way that when a query is run to find transactions that are kits (like lines 3-5) that it replaces it with the components of that kit (lines 14-17, 18-19,20-21 respectively)

The criteria for determining what are the kits would be to use the SHORT_ARTICLE_NO in the Transaction Table that would correspond to the SHORT_ARTICLE_NO in the Kit Table.

Does that help?

 

[CJ_London]

something like

SELECT L3_CUSTOMER_ID, L3_CUSTOMER_NAME_DIST, nz(componentproduct,product) as xProduct,nz(componentproductcode,shortarticleno) as articleno,nz(casesoncube,1)*sumoftransqty as quantity
FROM currenttranstable left join kittable on currenttranstable.product=kittable.cube

 

[jnolfo]

CJ, Thanks for this. Will look into this later today and get back to any other comments/questions I may have.

 

[jnolfo]

CJ, That worked exactly like I wanted. One last question. Any ideas on how I would combine this new query and a query that filters out the kits into one? I have new field names for the quantity.

 

[jnolfo]

OK, So I did a make table query on my original table with the filter and then a append query with the new table. Let me know if there is a better way or if I might be missing something on this.