Weird Question regarding report but might be in query

  • Thread starter ryan.fitzpatrick3
  • Start date
R

ryan.fitzpatrick3

OK I'm going to try an explain this to the best of my ability. I have
a query where I pull vendor numbers with sub vendor numbers. My report
that runs the query would look like this.


state vendnum vend desc sub vend

CA 000011 Vendor1
1234
1264
5874
state total 3

NV 000011 Vendor1
6548
state total 1


TN 000011 Vendor1
3456
1789
state total 2

There are states that have vendors and each vendor can have from 0-?
amount of sub vendors. I have a report that runs this query. I have a
count in the report that counts how many sub vendors per each vendor
per each state. That works good. The vendors that only have 1 sub
vendor though in the report I do not want to show up. So i would like
for it to look like this instead


state vendnum vend desc sub vend

CA 000011 Vendor1
1234
1264
5874
state total 3



TN 000011 Vendor1
3456
1789
state total 2

The vendors with one sub vendor are not on the report anymore. This
make sense? Now my question is how do I do this. I don't know how to
get rid of that vendors that have one sub vendor per state. If it's in
the query where do I do so.

Here is SQL:

SELECT Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM,
Qrywimssum.WIMS_SUB_VEND, Qrywimssum.SUB_VEND_DESC,
Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
FROM Qrywimssum
GROUP BY Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM,
Qrywimssum.WIMS_SUB_VEND, Qrywimssum.SUB_VEND_DESC,
Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
HAVING (((Qrywimssum.WIMS_SUB_VEND)>"1"))
ORDER BY Qrywimssum.VEND_NUM, Qrywimssum.WIMS_SUB_VEND DESC;



DST_CNTR is State
VEND_NUM is Vendor Number
WIMS_SUB is the sub vendor

If you're going to ask whats the >1 is for on the wims_sub, this
brings all sub vendor ID's greater than 1, because the sub vend
numbers in our system that are not used have a 0 in it, so this brings
in active numbers. It might not be the best way right now.

Any help.

ryan
 
K

KARL DEWEY

The first query finds those that have more than one.
Qrywimssum_Over1 ---
SELECT Qrywimssum.DST_CNTR, Count(Qrywimssum.WIMS_SUB_VEND) AS
CountOfWIMS_SUB_VEND
FROM Qrywimssum
GROUP BY Qrywimssum.DST_CNTR
HAVING (((Count(Qrywimssum.WIMS_SUB_VEND))>1));

Your query edited to use the first query ---
SELECT Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM, Qrywimssum.WIMS_SUB_VEND,
Qrywimssum.SUB_VEND_DESC, Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
FROM Qrywimssum INNER JOIN Qrywimssum_Over1 ON Qrywimssum.DST_CNTR =
Qrywimssum_Over1.DST_CNTR
GROUP BY Qrywimssum.DST_CNTR, Qrywimssum.VEND_NUM, Qrywimssum.WIMS_SUB_VEND,
Qrywimssum.SUB_VEND_DESC, Qrywimssum.SHIPPING_POINT, Qrywimssum.LAST_FM_DATE
HAVING (((Qrywimssum.WIMS_SUB_VEND)>"1"))
ORDER BY Qrywimssum.VEND_NUM, Qrywimssum.WIMS_SUB_VEND DESC;
 
R

ryan.fitzpatrick3

What do I do, I take the first one then create a new query for the 2nd
one then run the report off of the 2nd one?
 
K

KARL DEWEY

Yes. As I said 'The first query finds those that have more than one.'

The second query is yours that has been edited to use the first query.

Use it as source for your report.
 
R

ryan.fitzpatrick3

The report still shows

state vendnum vend desc sub vend

CA 000011 Vendor1
1234
1264
5874
state total 3

NV 000011 Vendor1
6548
state total 1

TN 000011 Vendor1
3456
1789
state total 2


It still shows the item number with divisions that have only 1.
 
R

ryan.fitzpatrick3

I looked at your new query, I think I know what you tried to do, you
have it where it searches state's that have over 1, so the result was
all 50 states(divisions same thing) and listed how many per state. so
it looked like this

CA - 1009
AR - 456
OR - 123
etc
etc

But since no states had 0 in it nothing was removed. I need to take
it one step farther. I need to attach vendor numbers to. this way the
divisions and sub vends are attached to vendor numbers. In my examples
above I show how certain vendors have certain states that have 1 or
10, I need to keep everything greater than 1. Is this possible. Thanks
for your help.
 
R

ryan.fitzpatrick3

OK I figured it out, I added vendor to your first query and voila it
worked. Thanks a million.

Ryan
 
R

ryan.fitzpatrick3

I got another question for you, I imagine it's real easy for you.

In a query I want to look for a certain item so I put like "000011"
and it pulls only that item code. Now if I don't want to look for an
item do I put

Is Not "000011" ?
I also want to look for mulitiple items such as 000011, 0000212,
000013 how do I do this?

Ryan
 
K

KARL DEWEY

I would use <> "000011" for one item.

Multiple items --
<> "000011" AND <> "000012" AND <> "000013"

Or another way ---
< "000011" AND > "000013"

You can also use a table if you got lots, maybe 50, by adding the table
SomeName with field [Not_To_Use].
Use criteria on field --
<>[Not_To_Use]
 
R

ryan.fitzpatrick3

Ok thanks!

I would use <> "000011" for one item.

Multiple items --
<> "000011" AND <> "000012" AND <> "000013"

Or another way ---
< "000011" AND > "000013"

You can also use a table if you got lots, maybe 50, by adding the table
SomeName with field [Not_To_Use].
Use criteria on field --
<>[Not_To_Use]

--
KARL DEWEY
Build a little - Test a little

[email protected] said:
I got another question for you, I imagine it's real easy for you.
Click to expand...
In a query I want to look for a certain item so I put like "000011"
and it pulls only that item code. Now if I don't want to look for an
item do I put
Click to expand...
Is Not "000011" ?
I also want to look for mulitiple items such as 000011, 0000212,
000013 how do I do this?
Click to expand...
Click to expand...
 
Top