Flattening Data From Two Tables - Using Query?

[wrayman]

I have two tables: Item Detail
pricing

And I would Like to use a query? to produce this result (QTY Table)



Everything I have tried produces multiple records for the same item.

I want to use the column heading (Field Names) to represent the QTY's 1 10 25 etc then from the pricing table do a range expression for example if from the pricing table the qty is 4000 I want it to go in the QTY table under 5000 (If between 2501 and 5000) So the result would be in the QTY table a total of 11 records that would have the pricing details of 33 records.

The tables have a one to many relationship one the naive_id, however I left that relationship out of the sample db.

I hope this makes sense as it is driving me crazy, I am sure I am missing something simple (again)



I have attached the actual tables in a db.example_1.accdb

[aytee111]

Are you totaling the prices? When you merge quantities, such as 2501 to 5000, what is the value of "price"?

Your Qtys table only goes up to 10000 - what about values greater than that?

[wrayman]

No I want the prices to correspond the field name as per the QTY Table example.

As mentioned in original post "I want to use the column heading (Field Names) to represent the QTY's 1 10 25 etc then from the pricing table do a range expression for example if from the pricing table the qty is 4000 I want it to go in the QTY table under 5000 (If between 2501 and 5000) So the result would be in the QTY table a total of 11 records that would have the pricing details of 33 records."

If you look at the QTY table it has the naive_id and the prices in the QTY field, there needs to be an expression indicating where the prices go.

[aytee111]

I'm sorry, I still don't get what you are looking for. Would a cross-tab query be of any help as a starting place?

TRANSFORM Sum(pricing.Price) AS SumOfPrice
SELECT pricing.naive_id
FROM pricing
GROUP BY pricing.naive_id
PIVOT pricing.QTY;

[wrayman]

Yes, that is what I am looking, what you have done is taken the 36 lines in pricing and transposed them match the item.

Now here is where the issue comes in, you have created the field names with the values BUT I want to use the Field names in the table QTY (pre-defined) AND put the prices in the ranges.

For example you see that there is a value of 61,500 I would put that value of that price in the QTY table 10000 so I would need an expression or statement that would put that there.

[aytee111]

You need a pre-query that will give you a new heading to use in place of the QTY. I have started you off here with the "IIf", you can add the other values that you need. Then use this query in place of the pricing table for the crosstab query.

SELECT pricing.ID, pricing.naive_id, pricing.QTY, pricing.Price, IIf([QTY]>10000,"10000",IIf([QTY] Between 2501 And 5000,"5000",[QTY])) AS QtyHeading
FROM pricing;

[wrayman]

I already have a table with the QTY,s I want Don't need to recreate them just need to place the prices in the appropriate spot.

[aytee111]

Use the QtyHeading to know which field to use on the QTY table. Is this an append query?

[aytee111]

Append : INSERT INTO QTYS ( naive_id, 1, 10, 100, 1000, 10000, 250, 2500, 50, 500, 5000 )
SELECT Query1_Crosstab.naive_id, Query1_Crosstab.[1], Query1_Crosstab.[10], Query1_Crosstab.[100], Query1_Crosstab.[1000], Query1_Crosstab.[10000], Query1_Crosstab.[250], Query1_Crosstab.[2500], Query1_Crosstab.[50], Query1_Crosstab.[500], Query1_Crosstab.[5000]
FROM Query1_Crosstab;

[wrayman]

You have the concept but we aren't clicking on what needs to happen.

This is very close:

TRANSFORM Sum(pricing.Price) AS SumOfPrice
SELECT pricing.naive_id
FROM pricing
GROUP BY pricing.naive_id
PIVOT pricing.QTY;

The QTY table is static that is where the values (prices) go

Your Query looks like this:

You used the QTY in the pricing table to create the fields, which flattens the data, great but since this data is coming from a web site I have know Idea what quantities there will be, so I need to create a workable range, which is what I did in the QTY Table.

So with your SQL instead of creating the fields "QTY NUMBER" it needs to assess the price that corresponds with the quantity in the pricing table and then put those prices in the QTY table under the appropriate QTY using expressions to determine where the price goes.

For example naive_id 73cd70930b611b2d4fb000e6f61ef626 has a qty of 61,500 and a price of 0.0782, the 61,500 needs to be evaluated and then place the price, 0.0782 in the QTY table under 10,000, I know that doesn't make sense, but it will work because more than likely we will never order 10000, and if that happens we will search the naive_id and check the supplier

This is designed to give a quick look at pricing in a sub form so all the pricing data needs to be in one row.

Please don't give up on me, you have the concept down pat, I am just not explaining it well.

[aytee111]

Or I'm not hearing it! I don't see the difference in your line for naive_id 72... and mine in the pictures above, except for some cosmetics.

[aytee111]

You can always write it in VBA

[wrayman]

Nope not on that line because the QTYS from the Price table line up to my predefined ones in the QTY table and that is why I say you have the concept down, the only issue is dealing with those values (prices) that fall out of the QTY table's fields.

But if you look at 73cd70930b611b2d4fb000e6f61ef626 it won't line up to my QTY Table because it has a value of 61,500 which isn't a field in my QTY table SO this price needs to go into the the 10000 field.

[ssanfu]

Maybe this?? (VBA)


Be aware the Access Gnomes sometimes get persnickety when field names begin with a number. Would "Q1", "Q10", "Q25",..... work for field names???

[wrayman]

Bingo! Yep that's it, Is there a way to append a table with that code? Specifically the QTY table?

I don't see how you got that query to work?? What am I missing?

And yes, Q1 , Q10, .... will work fine, thanks for the tip.