How to Avoid, repetition in one filed of Access Query...

[danireh]

Hi All, I am kind of stuck on finalize a crucial report in MS Access..... and tried to Google it as well but didn’t find any useful solution....
I will try to explain it with an example...


Lets Say i have a Table in Access with below data:
Table 1:
[TABLE="width: 218"]
<tbody>[TR]
[TD]Material
[/TD]
[TD]Description
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]AXYZ
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]BXYZ
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

and its linked with below table of deliveries... with Field "Material"

[TABLE="width: 255"]
<tbody>[TR]
[TD]Delivery
[/TD]
[TD]Material
[/TD]
[TD]Delivered Qty
[/TD]
[/TR]
[TR]
[TD]Del100
[/TD]
[TD]A
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Del100
[/TD]
[TD]B
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Del200
[/TD]
[TD]A
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Del200
[/TD]
[TD]B
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

Now while creating a query on both tables with Ordered qty and Delivered Qty, results are displayed as below...


[TABLE="width: 384"]
<tbody>[TR]
[TD]Material
[/TD]
[TD]Delivery
[/TD]
[TD]Order Qty
[/TD]
[TD]Delivered Qty
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Del100
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Del200
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Del100
[/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Del200
[/TD]
[TD]10
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

i want to avoid repetition in Order Qty column in final output as it gives a wrong image of order qty to be double or multiples of No. of Deliveries.

 

[Joe4]

That is because you have a one-to-many relationship between your two tables if you are just joining on the Material field.

So, the question is, what do you want your output to look like? Note that each material has two different Delivery values. So if you only want to see one, record, which Delivery value do you want to see?

 

[danireh]

Hi Joe,

I need my query to display to show order quantity only once, with all the other fields in tact. current result is displaying an order quantity with each delivery information which can lead to misunderstanding of q

[TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD]Material[/TD]
[TD]Delivery[/TD]
[TD]Order Qty[/TD]
[TD]Delivered Qty[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Del100[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Del200[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Del100[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Del200[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

 

[danireh]

Hi Joe,
posted by mistake without completion....

I need my query to display to show order quantity only once, with all the other fields in tact. current result is displaying an order quantity with each delivery information which can lead to misunderstanding of doubled Order qty.... the tables are joined in query with the field "Material" ...

[TABLE="class: cms_table_cms_table, width: 384"]
<tbody>[TR]
[TD]Material[/TD]
[TD]Delivery[/TD]
[TD]Order Qty[/TD]
[TD]Delivered Qty[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Del100[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Del200[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Del100[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Del200[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Hope that would help to understand the issues...

 

[Joe4]

Hmmm...

If you wanted to show something like that, you would probably need to do more of a Report than a Query, where you can incorporate some VBA code that looks at the previous record's results. You really cannot do that easily with a query (I am not even sure how you would do it with a query).

Or you could use VBA and loop through the recordset and write the results back to a new table...