First of?

[Ofina]

I have a report that I have written for Excel, using MS Query. It is an order log and supplier is one of the fields. I am doing it as a group by mostly.

Some of these orders may have multiple suppliers depending on the item.

How can I:
1. Get it to ignore one of the suppliers completely and only show the other one? In this case, the one I want to ignore should always be the last item on the order. Is there a First of function that I can use, or something similar?
2. Get it to show both in one field? 'supp1/supp2' or something like that?

 

[genomon]

Why aren't you doing this in Access? Super easy...

Ever notice how fast Windows runs? Me neither.

 

[SkipVought]

Hi,

"ignore one of the suppliers completely "

Change your SQL criteria to exclude that supplier...
[tt]
Not Equal ... selected Supplier
[/tt]
"Get it to show both in one field? 'supp1/supp2'"

Are there only ever two suppliers? Sometimes 3 or more? Maybe in the future?

You could Pivot on the resultset, putting the OrderID in a PAGE field and the Supplier in a COLUMN field.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ofina]

I'm not doing it in Access because I was asked to do it in Excel.

But, how would I do it in Access? Then maybe I can take the sql code behind it and learn from that.

 

[Ofina]

Not Equal ... selected Supplier"
Then it would remove *all* instances where that supplier exists. Sometimes I want that to be the one to show if it's not the last.

"Are there only ever two suppliers? Sometimes 3 or more? Maybe in the future?"
There could actually be any number of suppliers.

"You could Pivot on the resultset, putting the OrderID in a PAGE field and the Supplier in a COLUMN field."
I want this to be a one-stop report, that no one has to play around with after it's run.

 

[genomon]

If there is some other sequential numeric ID field, could you include/exclude using DMax() or DMin()?

Ever notice how fast Windows runs? Me neither.

 

[Ray1127]

If you could clarify your question it would be easier to answer.

If this is to track your company's orders why would you have multiple suppliers on 1 order that doesn't make sense. You can purchase the same item from multiple companies but each company would get a separate order. If this is to track your suppliers to determine which company get's the order why would you suppress any suppliers. You would want to include all of them so you can order from the one that gives you the best deal. What is it you're trying to accomplish????

 

[Ofina]

Just take it at face value.
These aren't our purchase orders, these are our sales orders. The supplier in this case is the supplier of the particular item on the sales order. Since it's an order to us it can have multiple suppliers per order.

And, it's a bit of a long story why I want to suppress one supplier. But, actually it's one item, (usually) the last item, that I want to suppress because it's not really an item (it's duty), but has a supplier associated with it and it's throwing off my log.

If I just say where item<>'duty' then it requires a group by clause for item which, of course, winds up giving me one line in my log per item, which is not what I want.

I suppose one way around it is to create a view in my database where I exclude all instances of duty and then use that view in my log.

 

[Ray1127]

If it was my project that's the way I would do it. Create a view in the database and use MS Query to get the data from the view.

 

[Ofina]

Okay, I have made a view to get rid of the line with the supplier that I don't want in my log.

But I still haven't figured out part 2 of my original question.

Say I have:
order item supplier
1 a aa
1 b aa
2 a aa
2 b bb
3 a aa
3 b bb
3 c cc

What I would like in my log is:
order suppliers
1 aa
2 aa/bb
3 aa/bb/cc

When more than 1 supplier it would usually be only two, but we could, in theory, have any number of suppliers there.

Any thoughts?

 

[lionelhill]

If you really, absolutely, must use Excel, it's fairly straightforward. First make a new column containing the concatenated text, using a formula such as:
=IF(A2<>A1, C2, IF(C2<>C1, CONCATENATE(D1,"/", C2), D1))
(this formula is taken from cell D2 of the following table, which has its top left corner in A1)

order item supplier
1 a aa aa
1 b aa aa
2 a aa aa
2 b bb aa/bb
3 a aa aa
3 b bb aa/bb
3 c cc aa/bb/cc
4

Note that I have added a dummy order number at the end. Unfortunately we'll need this later. Otherwise the idea of the formula is to start a new set of concatenation every time the order number changes, and add a new entry to the concatenation every time the supplier changes.

Now, in a new place (new worksheet?) put your order numbers in a column, and locate each corresponding concatenated bit of text with a formula such as
=INDEX(D, MATCH(G2+1,A:A, 0)-1)

The idea of this is that it looks for the next order number onwards, goes back one line, and returns the concatenated text from there. That is why it needs the dummy entry: to find the last order, it has to have a way of finding the next order that would exist, before going back one line.

 

[Ofina]

Well, you have completely lost me. But, I'm not looking to do this in Excel per se. I'm looking to do this in MS Query with the results returned to Excel.

So, what I am looking for is SQL code that will pull the information from my sql table example and spew it out in my perferred format into Excel.

Forgive me if I'm wrong, but I can't see how the above does that.

 

[SkipVought]

Leveraging off of lionelhill's suggestion, yet another column NUMBERING each group.
[tt]
order item supplier Seqn
1 a aa aa 1
1 b aa aa 2
2 a aa aa 1
2 b bb aa/bb 2
3 a aa aa 1
3 b bb aa/bb 2
3 c cc aa/bb/cc 3
4
[/tt]
using this formula
[tt]
=if(a2=a1,e1+1,1)
[/tt]
then in your query

select Max(F5)
From [Sheet1$]
where F5<3

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops
[tt]
select Order, Max(F4)
From [Sheet1$]
where F5<3
group By Order
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ofina]

Does anyone know how I can do this using the actual SQL of the query instead of in Excel?

 

[SkipVought]

Please explain what application you want to use instead of Excel and why.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ofina]

As I think I have said twice already I am doing this using MS Query with the results sent back to Excel.