spaceclown
|
| Posted: 02/07/2002, 9:42 AM |
|
Hello all,
Project needed is a way for registered users to sign up there children for baseball. Simple , sure but depending on there year born price is different. 1982-1994 is 65.00, 1995-1997 is 35.00 all others are not eligable. Add $40.00 for misc fees.So Heres is what I need. 1. Form to fill user info (players table)2. ability to register infinite number of kids 3. form showing all orders and add all order prices to a total. 4. Put the total into the users table 5. Users can only view the players they assigned (use session).
So all you smart peoples what approach should I take I have tried but continue to fail. I used the Bookstore approach but got a road block, I can calculate the total and add the 40 with a SELECT statement (SQL) but I can not pass the total to the users table, column named total. here is the statement,
select member_id,
iif (sum(quantity*price)>100,100,sum(quantity*price)) as sub_total,
(sub_total+40) as total from items,
orders where orders.item_id=items.item_id
group by member_id
So any ideas????? Please help I have to have this in production Monday Morning
database= Access 2000
lang= ASP 2.0 (no templates)
|
|
|
 |
kenlyle
|
| Posted: 02/07/2002, 9:00 PM |
|
Hey spaceclown!
You are in luck because you are using Access directly. The nice thing is that you can base a page directly off of a query.
I've whipped out a database structure that seems to do the basics of what you need pretty painlessly. It has the nice feature of asking for the player's birthdate rather than year, in case some kids are too young by age, not by year. Anyway, DLookup(Year(Age)) into a Prices table gives the result you want.
Here's the db:
Table: Members Page: 1
Columns
Name Type Size
MemberID Long Integer 4
Member Text 50
Table: Players Page: 2
Columns
Name Type Size
PlayerID Long Integer 4
MemberID Long Integer 4
PlayerFname Text 50
PlayerLname Text 50
PlayerBirthDate Date/Time 8
Table: Prices Page: 3
Columns
Name Type Size
PriceID Long Integer 4
Year1 Long Integer 4
Year2 Long Integer 4
Price
And here's the query:
SELECT Members.MemberID, Players.PlayerID, Players.PlayerFname AS [Registration For], DLookUp("[Price]","Prices","[Year2] >= " & Year([PlayerBirthDate])) AS Expr1
FROM Members INNER JOIN Players ON Members.MemberID = Players.MemberID
WHERE (((Members.MemberID)=1));
It should be easy to just add $40, to restrict the total to less than $100, etc.
Does that help?
|
|
|
|
|
