The question about SQL

[VadimOM]

Could you help me. I have 2 collumns: The first collumn contains ID of a client and the second collumn contains the amount of money he has to pay and at the same time it contains the amount of money he had already paid. E.g.

ID _______________ ________ Second Column

12586__________ ___________ 1023
12586__________ ___________ 2035
11458__________ ___________ 1456
11458__________ ___________ 2684
............... .......

That is the first collumn contains the ID of the same client two times: the first to show the amount he has to pay and the second to show the amount he has already paid. But we know that the amount he had already paid is below the amount he has to pay, i.e. if we have two rows:

ID _______________ _______ Second Column
12586__________ _________ 1023
12586__________ _________ 2035

Then 1023 is the amount he has to pay and 2035 is the amount he had paid. I know how to do it in Excel, but I have a database with millions sclients. How can I do it in access? I need to make a new table with 3 colums:

ID Paid amount Has paid

I.e. to transform the table:

ID_____________ _____Second Column

12586__________ ____1023
12586__________ ____2035
11458__________ ____1456
11458__________ ____2684
into
ID_____________ ____Paid Amoint_________ ________Has Paid
12586__________ ____2035_______ _______________ _1023
11458__________ ____2684_______ _______________ _1456
I know it is easy but I'm not so good at SQL yet.

[mwasif]

Hello and welcome VadimOM,

This question is related to MS Access. I am moving this thread to the relevant forum.

[Stewart Ross]

Hello VadimOM. Sorry to tell you that you are missing a field from your table. You need some way of identifying the type of the amount involved - in your case perhaps a yes/no field for 'has paid' would do. Without this there is no easy way to separate out which line is which in SQL - despite what you say about it being easy. SQL has no concept of record position, so the fact that 'has paid' rows come before 'amount to pay' rows is irrelevant.

Until you put some kind of identifier into your table as mentioned you will not be able to move this one forward.

Please post back to let us know what you have done.

In the meantime, I think you would undoubtedly benefit from learning about proper relational table design, and our introductory article on database normalisation and table structures may help you there.

-Stewart