Comparing two fields in a query?

Thanks Bob

Brian

No problem - I just happened to have that one from a previous post. I've been trying to create a library for myself on my own website so that I can refer to them at anytime for any of these posts. As they say, "a picture is worth a thousand words."
 
Worked like a charm thank you all so much I will probably be asking a lot of questions this summer.

Im glad for all your help on this.
 
I have another question that I think will be alot more difficult to accomplish

With one of the process that I had to get the last date for there are multiple different dates that that type of thing has taken place on. The process in the present database is called by two seperate names and there are two queries with all of the information in it.

Now heres the part that i have no clue how to work out. I need to count the number of each occurence of the thing that Im doing thats the same and then put the number beside that particular thing.

Say for instance i have

12341201 12/20/1995
12341201 1/13/1997
12341201 6/8/2000
12341201 9/2/2005

I need to be able to put an as output 12341201 - 4

Some of these things have never had the process done and others have had it done 4 or 5 times.
There are about 5000 of them so doing each different one individually would not be practical.
 
Its another simple total query

Groupby on Thing and another field FldCount:Count(*) with expression selected from the drop down.

Brian
 
Its another simple total query

Groupby on Thing and another field FldCount:Count(*) with expression selected from the drop down.

Brian

Is there anything I can use to make those things where there is no date show as 0 times.


Here is a somewhat better illustration of what I would possible like to have.

12341201 12/20/1995
12341201 1/13/1997
12341201 6/8/2000
12341201 9/2/2005
12341202 12/20/1995
12341203
12341204 6/8/2000
12341204 9/2/2005

What I need is for it to look like this
12341201 4
12341202 1
12341203 0
12341204 2
 
Last edited:
So you want to count the thingies except when a date is null, this is quite different. Still Group on Thingy but your expression will be something like
Sum(IIf(Not IsNull([datefield]),1,0))

Brian
 
So you want to count the thingies except when a date is null, this is quite different. Still Group on Thingy but your expression will be something like
Sum(IIf(Not IsNull([datefield]),1,0))

Brian

Ok so i tried this and it will not work for me. i keep getting the wrong numbers so I was wondering if what I could use if all I had just thingies and the date of each of the two different names that makes up the process for instance if I do two queries one with what one of the process is called and then another with what the other is called could I just have the end date for a bunch of thingies and some of them have been done more than once I just need to count the number of times that I have done each so this is what i have now



A query with thingies and I guess I need an expression to count each time there is a date with for each thingy and then show the number of times that I counted that.

Im going to try and write down in words what I have a bunch of thingies that have a process done to it every so often and each time its done the date is recorded. The big problem is a few years back they changed names of the process which is the same thing its just being called something else so what I need to be able to do is count the number of times that the process has been done to the thingies and display this next the the number of the thingy.
 
Last edited:
If you have a table or query called tblThingies

fldThingies fldDate
12341201 12/20/1995
12341201 1/13/1997
12341201 6/8/2000
12341201 9/2/2005
12341202 12/20/1995
12341203
12341204 6/8/2000
12341204 9/2/2005

Then Sql
SELECT tblThingies.fldThingies , Sum(IIf(Not IsNull([fldDate]),1,0)) AS FldCount
FROM tblThingies
GROUP BY tblThingies.fldThingies;

you will get
12341201 4
12341202 1
12341203 0
12341204 2

But I am confused by what you are now saying so unless you can post your db I feel that I can no longer help.

Brian
 
Thats ok I figured out what was wrong. It was actually the data that I was querying from was incorrect so I had to go back and fix that first and now it works great. Thanks for all of your help with this.
 

Users who are viewing this thread

Back
Top Bottom