Results 1 to 7 of 7
  1. #1
    AlFor is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4

    Create New Table from 2 Tables and insert values sorted by rows and columns

    I'm very new to this and would appreciate some help

    I have 2 tables A and B

    A table contains: ID, Starting Station, Scheduled Departure Time, Final Station, Scheduled Arrival Time

    B Table contains records of vehicle with time stamp at certain stops for 24 hour period like this:


    Vehicle ID, Direction, Stop ID, Time

    I would like to make a query or a write a procedure or macro for resulting table to show vehicle's times through stops (Stops in columns) and ID vehicles in rows in ascending order by Dep Time like:
    Row 1: ID, DepTime, stop 1, stop2 stop3 etc to end stop, Arr Time
    RoW 2: ID, DepTime, stop 1, stop2 stop3 etc to end stop, Arr Time
    Etc.....
    Pls Note NOT All Stops have time recorded in Table B (some could be missing as GPS is not perfect )so the procedure should be able to add an empty or null value in the column if no adequate time was found
    Also recorded time values for end stops may actually exceed scheduled arrival time (I guess 15min late should be allowed for)

    Thanking anyone pointing me in the right direction

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    you need a common cross referencing field in both tables

    you can cross tab to a side-by-side display but I don't recommend it in general and definitely not for novices i.e. stop 1, stop2 stop3 etc to end stop

    database records normally will be
    stop 1
    stop2
    stop3
    etc

    and it is much more straight forward to display the data this way

  3. #3
    AlFor is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4
    Thank you for your reply. The ID is unique to both tables. Will that be enough. I really need to show rows with stop1,stop2 etc for one vech.
    Row 2 should show a different vehicle with different dep time. Can this be done in cross tab with time fields or a report query?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,728
    If there is no record for a stop then that stop will not have a column in the CROSSTAB query. This is why it is hard to build forms and reports based on CROSSTAB, the fields are dynamic. Forcing a column for every stop regardless of how the records are filtered even when there is no data for that stop is not easy. Review http://allenbrowne.com/ser-67.html#ColHead
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    AlFor is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4
    Sorry if I still do not understand May be I'm not explaining right.. You said "If there is no record for the stop". There is a stop record in table B under Stops ID for each Stop. There is total of 12 stops (1 to 12) and multiple records showing time when the vehicle passed by those stops in a 24 hour period. The vehicle ID in table B is related to vehicle ID in table A (Same unique Number). So how to fill and show data horizontally for each vehicle starting from stop 1 to stop 12 if for instance it was de depart say at 10 am.
    There should be a way to sort them and group them in traveling order for a certain time slot.
    I apologize if my questions make no sense.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,728
    As you stated in original post "Pls Note NOT All Stops have time recorded in Table B" - a CROSSTAB on this table alone cannot produce a column for each stop if a stop does not have data - not without some special tricks.

    Review the referenced link carefully. It has good guidance on working with CROSSTAB queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    AlFor is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    4
    Quote Originally Posted by June7 View Post
    As you stated in original post "Pls Note NOT All Stops have time recorded in Table B" - a CROSSTAB on this table alone cannot produce a column for each stop if a stop does not have data - not without some special tricks.

    Review the referenced link carefully. It has good guidance on working with CROSSTAB queries.
    Thank you for your time and help. So there could be a way if I only can find the tricks. Hmmm

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

Similar Threads

  1. Pivot Table - Moving Values from Columns to Rows
    By charlatain in forum Access
    Replies: 3
    Last Post: 07-21-2013, 01:41 PM
  2. Replies: 4
    Last Post: 06-21-2012, 05:39 PM
  3. Table rows to columns
    By Rhino373 in forum Programming
    Replies: 5
    Last Post: 12-22-2011, 01:44 PM
  4. Excel rows sorted when imported to Access
    By gwn in forum Import/Export Data
    Replies: 1
    Last Post: 06-15-2011, 08:34 AM
  5. Inner Join on Sorted Columns
    By Nobody in forum Queries
    Replies: 6
    Last Post: 08-24-2010, 03:28 PM

Tags for this Thread

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