How can I avoid multiple values 1

[aarellano]

Hello,
I have been wrecking my brain into this one.

I have a table that contains order number, operation #, quantity received, and hours.

for example

order# operation# qtyrc hrs
1234 100 10 2
1234 120 10 3
1234 130 10 2.5


I can get this, no problme. I am looking of a way to get this:

order# operation# qtyrc hrs
1234 100 10 2
1234 120 3
1234 130 2.5

Is there a way to do this with SQL or would I need anything else to accomplish the task?

please any healp is much appreciate it.


Thank you!!

 

[dhookom]

Why? Is this for a report? Are you sure this is one table, it looks like at least 2 tables combined one to many in a query?

Is there a rule that suggests which record should display the qtyrc value?

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

I gather that you want "qtyrc" to show only the first instance of the value and then show an empty field for the following entries where the "qtyrc" is unchanged.

There are ways to make that happen using UNION queries, coordinated sub-queries, TOP predicates and such, but this is really a reporting issue. Most reporting systems have a simple "Suppress Duplicates" switch that does that.

 

[aarellano]

well this is where it becomes a bit tricky because I have been asked to present the data in this fashion so it can be manipulated in excel. so it is like a report but not really a report.
any good inexpensive reporting software out there?

 

[PHV]

Why not simply let excel doing its own presentation stuff ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Access reports can do that.

Use your query as the data source for an Access report, set the "Hide Duplicates" property of the field to "Yes" and export the report to Excel.

 

[aarellano]

Golom,
That is what I ended up doing. I just wanted an easier way. Thank you all for the help. I really appreciate it!!