Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2011
    Posts
    11

    Unanswered: access database help

    hi! i have a project to due which i am almost done with it. but when i showed this to my prof . he asked me a question how do u know if the car is available or not..i really need help ...thanks..

    do i need to make a new field like leaving time..

    he gave us 3 zones A , B , C
    6 DRIVERS 4 OF THEM ARE COMPANY DRIVERS 2 OF THEM OWNS THE CAR.
    all helps appreciated...
    Attached Files Attached Files

  2. #2
    Join Date
    May 2010
    Posts
    601
    In the real world a good developer would ask the users/customer what they want. In your case your customer/user is your prof.

    PS: What prof./teachers want and what is done here in the real world can be very different. We could show you how we do it in the real world but that might not be what the prof wants/ So you get a bad grade.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Mar 2011
    Posts
    11
    thanks for the reply....

    i didn't ask u to do the whole project. all i wanted is some advise...

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by d3vil112 View Post
    thanks for the reply....

    i didn't ask u to do the whole project. all i wanted is some advise...


    All I was trying to do was give the best advice I know.

    PS: I have tutored many students.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    If you do not elaborate your question a little more by providing some explanations about your project and the problem you have to solve, your chances of receiving an aswer that could help you are very few .

    Everybody is not ready to dig in an unknown database and spend time trying to figure out what you want precisely.

    See: http://www.dbforums.com/microsoft-ac...ml#post4534485
    Have a nice day!

  6. #6
    Join Date
    Mar 2011
    Posts
    11
    hi thanks for showing interest. the question i asked before i managed to answer myself...

    there is another problem i m having...
    there are six drivers and six cars . two drivers owns the car while four other drivers drives companies car.
    those who drives their own car gets 70% of the money back n those who drives company car gets 50% of the money.

    i did something like that..
    Invoice Paid: [Cost iN Dollars]*0.5
    cost in dollars is the total what they make.* 0.5 for the company car drivers . for example 30 * .5 = $15 that's how much a company car drivers make for one ride.
    do i have to make two different invoices for those who owned car n companies car or do i have to add them in the same coding...


    i hope i make sense..

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Don't make different invoices. Add a column (field) into the Drivers table containing the multiplicator you use into the computation. This way it would be easier to update the system if the rules change (say 50% becomes 45%). This way you'd just have to modify the values into a table as nothing is hard coded. Ideally these rations should be stored into a separate table, though.
    Have a nice day!

  8. #8
    Join Date
    May 2010
    Posts
    601
    I agree that you do not need to make two separate invoice tables.

    To properly designing/normalizing the database it will need to handle this:

    When the rate changes it should not effect any existing records.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Mar 2011
    Posts
    11
    i finally made it... thank u guys...

  10. #10
    Join Date
    May 2010
    Posts
    601
    Thanks for the update!

    Great job getting it figure out. Hopefully learning something too.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    Mar 2011
    Posts
    11
    hi back again with another question ...

    I HAVE TO ADD THREE DIFF TIMES BUT I ONLY KNOW HOW TO USE ONE.

    three zone TIME
    A 35 MIN
    B 45 MIN
    C 90 MIN

    DateAdd("n","35",[CarOutStatus]![Time Depart])

    RITE NOW ITS SHOWING 35 MINS DIFF FROM THE CURRENT TIME.
    I THINK I NEED TO DO SOMETHING WITH THE ZONE N TIME TO CALCULATE..

    ANY SUGGESTIONS.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    The clever way ( according to my mind) consists in creating a Lookup table with the time zones: Tbl_TimesZones with (at least) 2 columns: TZ_Code, Text(1), Primary key and TZ_Value, Long. In SQL we can then use:
    Code:
    SELECT DateAdd("n",[TZ_Value],[TimeDepart]) AS TTime
    FROM Table1 INNER JOIN Tbl_TimeZones ON Table1.TimeZone = Tbl_TimeZones.TZ_Code;
    Otherwise you'll have to use a serie of cascading IIf's which can be hard to understand (and to debug!) and that can quickly become unmanageable when the number of possibilities increases (only 3 in your question):
    Code:
    SELECT 
    IIf([TimeZone]='A',DateAdd("n",35,[TimeDepart]),
    IIf([TimeZone]='B',DateAdd("n",45,[TimeDepart]),
    IIf([TimeZone]='C',DateAdd("n",90,[TimeDepart]),[TimeDepart]))) AS TTime
    FROM Table1;
    Have a nice day!

  13. #13
    Join Date
    May 2010
    Posts
    601
    I agree with Sinndho.

    I would use a look up table to avoid hard coding values. Values are data which normally should be stored in a table.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  14. #14
    Join Date
    Mar 2011
    Posts
    11
    i have made my lookup wizard like this ..i have also made a field name Caroutstatus in which i have CarID, Zone, TimeDepart

    SELECT [CarOutStatus].[CarID], [CarOutStatus].[Time Depart], [CarOutStatus].[Zone] FROM CarOutStatus;

    in the qry i used

    SELECT IIf([TimeZone]='A',DateAdd("n","35",[TimeDepart]),
    IIf([TimeZone]='B',DateAdd("n","45",[TimeDepart]),
    IIf([TimeZone]='C',DateAdd("n","90",[TimeDepart]),[TimeDepart])))
    FROM CarOutStatus;

    its asking me for timezone n depart. i think i messed up.

  15. #15
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by d3vil112 View Post
    i have made my lookup wizard like this ..i have also made a field name Caroutstatus in which i have CarID, Zone, TimeDepart

    SELECT [CarOutStatus].[CarID], [CarOutStatus].[Time Depart], [CarOutStatus].[Zone] FROM CarOutStatus;

    in the qry i used

    SELECT IIf([TimeZone]='A',DateAdd("n","35",[TimeDepart]),
    IIf([TimeZone]='B',DateAdd("n","45",[TimeDepart]),
    IIf([TimeZone]='C',DateAdd("n","90",[TimeDepart]),[TimeDepart])))
    FROM CarOutStatus;

    its asking me for timezone n depart. i think i messed up.
    The point of the look up table is to avoid using the large IIF(). If you are using the look up table correctly your query would look something like this example that was previously posted:

    Code:
    SELECT DateAdd("n",[TZ_Value],[TimeDepart]) AS TTime
    FROM Table1 INNER JOIN Tbl_TimeZones ON Table1.TimeZone = Tbl_TimeZones.TZ_Code;

    Did you make a look up table for the Zones?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •