Help needed now posted twice and no reply!

[Hoganc]

Hi all

I really could do with some assistance with this it may sound quite simple but for some reason I cant do this?? and its driving me nuts!

I have a column name [policy] and a column name [movement type]

the rule is you cant have a policy number and different movement types
you can have many of the same but not different and there are about 8 movement types

I would like to find all the policy numbers where there are two movement types using a query.

Any help would be really greatful

Thanks in advance

C

 

[GaryPanic]

need a lot more to work with than this ..

 

[Hoganc]

Policy numbers = Customer number
there is also payment numbers = which are transactions which are unique and
then address etc details
also there is whether the payment is coming into us out from us or through us. During the month you cant have a combination only multiple payments out, through or in.

does this help?

 

[Mile-O]

By all means bump the original post, but please don't clog the forum up with the same question over and over.

 

[Hoganc]

thanks for the help there!

 

[GaryPanic]

I think you need a count function and some where statements - I would not be able to think this one through -

from your post you want to be able to count
policy which have movements (count on this ) and anything greater than the count of 2 report on ..

one for the advacned guys on this

might help if you strip your d/b down and post it (put some dummy entries in it)
g

 

[Hoganc]

Hi Gary

thanks for that, much appreciated.
Data is

Policy/customer number Movement transaction (unique)
11111 in 1
11111 in 2
22222 out 3
33333 through 4
44444 in 5
44444 out 6
The bottom ones i want to see and not the rest -

hope this helps - anyone advanced in here?
Thanks c

 

[Pauldohert]

11111 in 1
11111 in 2

The policy number is 11111?

The movement trabsaction is "in 1" or "in 2" - These are helds as a single fileld string?


why do you not want to see these - the policy number has two mevement types doesn't it?

 

[Hoganc]

sorry that was supposed to be false data for you to understand the

column headings are...


policy numbers, movement types, transaction number the number

whether the payment goes into company out or through -

 

[GaryPanic]

I am sitting this one out - cos' I don't quite follow it
g

I think you are going to have to write out a lot more that what you haven us at the moment .

something is not quite right with this either I am not getting it or your set up is slightly askew ...

normally
1 policy
pol number 12345
movement (or type of transaction)
can be 1 of 6 options or 8

I would of done
Credit -
Debit
handlingcredit (through)
handlingdebit
internalcredit
internaldebit

I just cannot get my head round it ..

 

[GaryPanic]

44444 in 5
44444 out 6
thse cancel each other out . right ?

 

[GaryPanic]

on a cancel option there is a better route to take .

have as an option a reverse - and have anothre transaction type called internal-correction etc.

now as you have a transaction set up a qry to do an autoreverse by *-1 if the number is a psotive then *-1 will credit it it and if its a credit then*-1 will make a psotive - and have an update funtion on a correction screen to mark these transactiosn both as (9) internal corrections-

now - if this is so be care full - as month ends will throw you
if this is what you are after -corrections in Jan will be ok- ie trans done in jan and corrected in jan fine - but trans done in jan and corrected like above in feb will throw accounts (assuming a month by month accounts system)
two routes on this option have a allow yes/no option in your underlying table and every month push a button at month end and this will flip the allow corrections to no - but ones done after pushing the button will be default to yes and you can correct or put a message in the form telling people not to do different month corrections - I prefer the first option - fixed and no room for error - the second does allow flexibility - but your reports -accoutns might be tits up??

depends on the type of busines and the reporting critia ..

g

 

[Pauldohert]

sorry that was supposed to be false data for you to understand the

column headings are...


policy numbers, movement types, transaction number the number

whether the payment goes into company out or through -

Im none the wiser !

 

[Hoganc]

Hi G

in a single table there is
customer numbers (or policy numbers) - these people can have lots of policies
transaction types - 8 transaction types in all - debit, credit, cheque payment etc.

and what I am trying to do is find all the customers numbers where they have more than one transaction type within a period. As they cannot have both credit and debit due to the way it is set up (or at least they shouldnt) I dont work for a bank so this is only information to try and assist.
Other details in the table include address but these can be the same so the only unique number is a combination of transaction number and customer id (a concatenated key)
any ideas?

 

[GaryPanic]

what field are you in ??

while its not normal to have a debit and a credit - it is possible
I am in insurance - and i can have an amount due and also a credit due at the same time (and rarely on the same policy)

if you are after statments -invocies etc on reports then there are better ways of doing this than the one you are going down

on your report allow debits -credits and tally them up and you end up with a net result


but this does make a bit more sense -
so policy number 12345 could have six transaction types of "in" which is fine - but you need to know if 12345 has 5 "in"s and 1 "out" and this you need to know...


ok you need to let us know

if you have a "in" then you need to know if there's an "out"


right now lets look again
list the vlaues of in and its opposite out
for each
i.e is in and through acceptable

we need to know the opposites to make this work


in and its opposite is out
through and its opposite
etc

this is going to be beyond me - but its needs to be presented as clearly as poss so one of the bright people can digest this . and come up with a very simple answer - as I said this is going to be and i"f" statement solution
if x is in then ... a lot of coding on this one
I don't do coding I hack it around to do what I want - but coming up with original coding - nope needs someone with brains.

g

 

[The_Doc_Man]

Let's see if I can state the problem - and then maybe work on the solution.

You have a code number, an action, and a second transaction number. It is not clear whether these are dated and grouped according to some attribute of the date. So I'll make no assumptions there. We'll just say that your problem is based on some data "domain." Whether "for all time" or "for this month" is merely having or not having a GROUP BY DATEPART(...) anyway.

You allow any single code number to have any number of records that differ by the transaction number. You do NOT allow a single code number to have two types of transaction in the data domain.

Do this in two stages.

First, call this qrySU.

SELECT [Account], [TransType], COUNT( [TransNumber]) FROM domain GROUP BY [Account], [TransType];

Now SELECT [Account], COUNT( [TransType] FROM qrySU WHERE COUNT( [TransType] ) > 1 ;

When you cannot do it directly, do it in layers.

 

[WayneRyan]

C,

I don't really understand, but ...

SELECT [Account], 
       [TransType], 
       COUNT(*) 
FROM domain 
GROUP BY [Account], [TransType]
Having Count(*) > 1;

Wayne

 

[Hoganc]

Hi all,

thank you very much, thats done it... how you worked it out I have no idea but I am very greatful

thank you!!

 

[The_Doc_Man]

Wayne's solution is tighter than mine. I was shooting from the hip.

 

[Hoganc]

Thanks Wayne too much appreciated. ..

I do have another question for both of you then.... may be should post a new thread.

I would like to run a query different one
and have the results of the query listed and then ......

remove all the occurances of a name from the query but the names of the people i want to omit are in another table?

Is this possible? I know i can hard code it in but i may need the users to change the names

thanks