Results 1 to 10 of 10
  1. #1
    RobinAK is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    4

    How to create table?

    Hello



    I have the table and 2 records. Tell me who has the car in month

    name of car Audi Audi
    owner John Donald
    from month 1 4
    until month 3 12
    version 1 2

    When any car change owner i create new version with new owner for specific months

    I want to create new table and creates records
    For this example, creating 12 rows

    A want to do it in SQL(query) better then in VBA

    How to do it, who can help me?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you want 3 tables:
    tPersons, to enter everybody using cars
    tCars , all about the 1 vehicle
    and tCarRental
    this holds who has the car when

    tCarRental:
    CarID (long) from tCars table
    PersonID (long) from tPersonID
    StartDate
    EndDate
    DateReturned

    I would also put, in the tCars table
    OwnerID (long)

    this would show the current owner, and changes when the car gets swapped.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need three tables, not one

    tblCars

    CarPK - autonumber
    CarVIN - text
    CarModel - text

    tblOwners
    OwnerPK - autonumber
    OwnerName - text

    tblOwnership
    OwnershipPK - autonumber
    CarFK - long, link to tblCars
    OwnerFK - long, link to tblOwners
    FromDate - date
    ToDate - date

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Snap

  5. #5
    RobinAK is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    4
    Thank you for your quick answer
    A make 3 tables with all information

    But still have to make new table with 12 rows for one car in one year.
    How to do it?

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You don't - You simply record the from and to dates.
    If you want to display availability over time you would need to create a Form with the dates showing, this is not a trivial task for a new user.
    Google for Access Calendar booking examples and have a look in the sample databases here.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    RobinAK is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    4
    The problem is a must show the result in Qlik for every month separetely.
    User choose one seperate month

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you provide samples - show us what the data will look like beforehand, and what you want it to look like afterwards.

    If you want one line for each month of the year regardless of the data, then that will have to be a table (or a VBA routine).

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    RobinAK is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jan 2018
    Posts
    4
    I must to think it over and will come back to you with samples

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 05-26-2016, 05:13 PM
  2. Replies: 1
    Last Post: 03-31-2016, 03:33 AM
  3. Replies: 3
    Last Post: 06-04-2015, 09:48 AM
  4. Replies: 1
    Last Post: 05-20-2013, 01:45 PM
  5. Replies: 2
    Last Post: 12-20-2011, 07:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums