Query or VBA Help

[pj33558]

I am trying to build a database and am learning a lot in process. Been a very long time since I've done this sort of work... VBA used to come naturally, as did SQL... now, I'm a little Any help is GREATLY appreciated!



Currently, the data is pulled in from a G/L directly (ODBC). I have tried using an IF(Species="HORSE" or "DONKEY" or "GOAT", $,0) for the EQUUS field, and IF(Species="DOG",$,0) for canine field. Access at me .

Like this:

EXCEL - CURRENT

ACCESS - TO BE

fiscalno

name

training

species

amount

fiscalno

name

training

species

canine amt

feline amt

equus amt

201401

x

lunge

HORSE

5

201401

x

lunge

HORSE

5

201401

y

tricks

CAT

6

201401

y

tricks

CAT

6

201401

y

clicker

DOG

-10

201401

y

clicker

DOG

-10

201401

y

clicker

DOG

5

201401

y

clicker

DOG

5

201401

y

clicker treat

DOG

4

201401

y

clicker treat

DOG

4

201401

z

lunge

HORSE

6

201401

z

lunge

HORSE

6

201401

a

roundpen

DONKEY

6

201401

a

roundpen

DONKEY

6

201401

a

roundpen

DONKEY

9

201401

a

roundpen

DONKEY

9

201401

a

roundpen

DONKEY

10

201401

a

roundpen

DONKEY

10

201401

a

sugar

GOAT

2

201401

a

sugar

GOAT

2

201401

b

sugar

GOAT

2

201401

b

sugar

GOAT

2

201401

b

carrot

HORSE

3

201401

b

carrot

HORSE

3

201401

b

cookie

DONKEY

5

201401

b

cookie

DONKEY

5

201401

c

carrot

GOAT

10

201401

c

carrot

GOAT

10

201401

c

apple

HORSE

-20

201401

c

apple

HORSE

-20

[pbaldy]

I'd leave the structure intact; the proposed structure isn't normalized. In any case

IIf(Species="HORSE" or Species="DONKEY" or Species="GOAT", $,0)

[pj33558]

I'd leave the structure intact; the proposed structure isn't normalized. In any case

IIf(Species="HORSE" or Species="DONKEY" or Species="GOAT", $,0)

First, thank you for responding.

Second, the current structure is over 17,000 lines long. Per month. It has to be extracted into Excel, then filtered, then hand copy and pasted into the format requested, then additional columns added in for various numeric items, and then calculated. Right now, the current process takes about 3 weeks from start to finish and I'm trying to alleviate that through Access and via process improvement. I only have Access to use as a tool.

Because I can peer into the G/L directly via Access, I was formatting the query to give the requested format so that I could then just export out the result and give to requester. The issue is those three columns.

So, when you say normalized is that because I'm trying to make the IF statement peer back at the original field?

I apologize for asking really dumb questions, this project is far outside the scope of what I'm used to doing.

Thank you!

[pj33558]

That didn't work. I need all the data from one field trifurcated into three fields based upon qualifier in "species' field.

[pbaldy]

My point about normalization was based on the assumption that you wanted to create an Access table with that format. The multiple columns with amounts wouldn't be normalized. In a query you should certainly be able to achieve that look, and that should work (changing $ to the field name of course). I was only giving the complicated one, since you had the syntax wrong. In a query design view:

EquusAmt: IIf(Species="HORSE" or Species="DONKEY" or Species="GOAT", Amount, 0)

If that doesn't work, can you post a sample db here?

[pj33558]

Thanks again, Paul.

I tried that as well and it barfed at me.

So, I did a really horrendous - to you developers - thing and created queries that would pull the data I want for each column. Then I was going to use the table that links to G/L for columns 1-9, the queries for 10-13, and use a report to pull all that crap together

Again, I'm not a strong developer so I do appreciate your help.

In writing those queries:

4 columns

Col 1 has a not like with one item
Col 2 has a not like with one item
Col 3 has 3 not likes with and ... Not Like "Llama" and Not Like "Horse" and Not Like "Goat"
Col 4 has 3 not like with and ... Not Like "Treats" and Not Like "Clicker" and Not Like "Hay"

Everything ran exactly as I'd hoped EXCEPT for Col 3's "Not Likes" were ignored. The software pulled everyone of those instead of excluding them.

Is that too many "Not Likes" for Access?

Again, I do appreciate your help especially when I have no doubt you're wondering "wth" would I do things this way... let's just say, I was brought in to "band aid" a really baaaaaaddd problem until things can be formally redone with new software.

[pj33558]

Nvm;

Changed the Not Like to Not + And ---- worked like a charm.

Posting what I did because maybe some other newbie will come along and see they are not the only ones struggling to relearn/learn this tool

[pbaldy]

Glad you got it sorted, though still curious why the method posted didn't work.