Can someone help and show me how to display using these two tables combined into one using VB.net. I'm really struggling trying to figure this out after hours of work, I decided its time to ask for help, and have never had this much trouble with queries or any code for that matter!

So in SQL Server 2005 I have the following tables...

Students
PKId
Cart_Id
Fname
Lname
Credits (will not always equal one)


Items_Ordered
PkId
Cart_Id
Product_Id
Quantity (will always be one)

Essentially, for the Items_Ordered table there could be two that look like
1001 897778765 S5532 1
1001 897778765 S5532 1
Meaning there could be a quantity of two or more for an item, but they each use their own line.


Example of rows in Students table with corresponding Cart_Id
----------------------------------------------------------
PKId | Cart_Id | Fname | Lname | Credits |
----------------------------------------------------------
758 | 983772837 | Jon | Smith | 4 |
759 | 983772837 | Jane | Smith | 4 |
760 | 983772837 | Jimmy | Smith | 4 |
761 | 983772837 | Judy | Smith | 3 |
----------------------------------------------------------

Example of rows in Items_Ordered table with corresponding products purchased
---------------------------------------------------
PKId | Cart_Id | Product_Id | Quantity |
---------------------------------------------------
758 | 983772837 | 2344 | 1 |
759 | 983772837 | 2344 | 1 |
760 | 983772837 | 2344 | 1 |
761 | 983772837 | 4566 | 1 |
761 | 983772837 | 4566 | 1 |
761 | 983772837 | 3442 | 1 |
761 | 983772837 | 8876 | 1 |
761 | 983772837 | 9099 | 1 |
761 | 983772837 | 9099 | 1 |
761 | 983772837 | 9099 | 1 |
761 | 983772837 | 9099 | 1 |
761 | 983772837 | 9099 | 1 |
761 | 983772837 | 1332 | 1 |
761 | 983772837 | 7465 | 1 |
761 | 983772837 | 7465 | 1 |
---------------------------------------------------


The following is what has to happen...

The total amount of credits should always equal the total quantity of items ordered for that Cart_Id.

Now, the amount of Students there are for each purchase will vary from 1 - 4 and the credits need to be distributed evenly.

If there is an uneven Quantity versus Student count, then the credits will be allocated from top to bottom, adding an extra to each
of those students.

So when it comes down to it, I need Students.Fname and Students.Lname put into each line of the Items_Ordered table for that Cart_id, distributed evenly.


I would really appreciate any help on this, as I am almost brain dead now.

Thanks!