create query to display records sharing the same ID

[kevin19]

I have a table with a transactionID that has a payment type field for cash and card i.e. ID 1 payment type cash, ID 1 payment type card. These are 2 separate records. How do I create a query where I can identify the ID which has both cash and card payment type? Some ID only have cash; some only have card but some ID have both cash and card payment type.
E.G.
ID Type
1011 Cash
1012 Card
1013 Card
1014 Cash
1014 Card
1015 Cash

I tried putting in Cash and Card in the criteria in the query but it didn't work. I thought it will display all the IDs that have Cash and Card payment type.
Any help is greatly appreciated as I am kind of stuck right now?
Thanks

 

[Galaxiom]

Use an aggregate query. Group By ID and Count on PaymentType. Apply a criteria of >1 to the counted field. These will be the IDs with more than one payment record.

(I called the field PaymentType because Type is a reserved word in VBA and is best avoided.)

 

[kevin19]

Thanks Galaxiom

I have tried it but it is not returning just those records that have both cash and card payment types. I merely want to display all the records with those IDs that have both cash and card payment types and not records that have only cash or card payment types.
I am sorry I may not be clear in my initial post. I hope it is clearer now.
Thanks for your prompt response.

 

[Galaxiom]

I assumed that you have no more that one Cash and one Credit payment per ID. If you have multiples of either of these on an ID then a different query will be needed.

Otherwise if you construct the query as I described you should get the result you want.
Paste this into the SQL view. Change the table name to match yours.

SELECT ID FROM tablename
GROUP BY ID
HAVING Count([Type]) > 1

 

[kevin19]

Hi again
Sorry I do have multiple records i.e.
ID PayType Amount
101 Card £10
101 Card £5
101 Cash £20
101 Cash £10
101 Cash £15

I just need to return 1 record Id 101 Card £15 Cash £45
Can this be done?
Thanks once again

 

[Galaxiom]

You need a crosstab query.

TRANSFORM SUM(Amount) AS SumofPayment
 SELECT ID, PayType, Sum(Amount) As TotalAmount
 FROM tablename
 GROUP BY ID
 PIVOT PayType

 

[kevin19]

Hi Galaxiom
Thanks for your help. I am still not getting what I require. I am giving this a break for now as I have a more immediate problem to resolve first.
I wonder if this is simple.
How can I create a combo box that displays the TypeIDs in a form and after selecting from this combobox a typeID; it will extract and display all the records having the same typeID within the form based on a table as the source for the form?

I need to resolve this urgently. Please help if possible.
Thanks