By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

The question about SQL

P: 1
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.
Feb 17 '09 #1
Share this Question
Share on Google+
2 Replies


mwasif
Expert 100+
P: 801
Hello and welcome VadimOM,

This question is related to MS Access. I am moving this thread to the relevant forum.
Feb 17 '09 #2

Expert Mod 2.5K+
P: 2,545
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
Feb 17 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.