Not even sure how to title this question, to be honest...

[Stang70Fastback]

I'll try to do this as briefly as possible, but I would really appreciate anyone who has the patience to understand this and help me come up with a solution, if there is one.

Currently, I have a "Vehicle Assignment Board" in Excel, which allows a dispatcher to assign different vehicles to different routes. So you would have something like this

|Route| |Vehicle|
|10Rte| |Bus121|
|11Rte| |Bus124|
|12Rte| |Bus087|
|20Rte| |Bus082|
etc...

I can then link this table to Access, and merge it with another table which has route descriptions, along with driver assignments, shift times, etc.... I match the bus assignments based on the first few characters of the route descriptions, which are the same as the route column in the above table. So I end up with something like this

|Route Description| |Route Driver| |Vehicle|
|10Rte - Blue Line| |John Williams| |Bus121|
|11Rte - Uptown1| |Jake Doohicky| |Bus124|
|12Rte - Uptown2| |Bob Whatsits| |Bus087|
|20Rte - SouthBus| |Amy Bottom| |Bus082|
etc...

What I want to do is come up with a replacement for the Excel spreadsheet in Access. I have already designed a replacement Form in Access that has fields for all of the bus assignments. My problem is, I am not sure how to collect the data and get it to display in a way that I can make use of it. For example, I can set a Record Source for the form to be a "buses" table and I can designate each text box individually, but what I end up with is ONE record, with 50 columns for each route assignment. So this:

|10Rte| |11Rte| |12Rte| |20Rte|
|Bus12| |Bus14| |Bus08| |Bus56| etc...

That's not necessarily a problem in its own right, I can't do any table merging with the data in that format. I need to have it in two columns, as I showed above, as opposed to the other way around. Is there a better way of going about what I am trying to do? Does this even make sense?

 

[JHB]

Why are you not doing the same design for the form as you have done it in Excel?

|Route| |Vehicle|

 

[Stang70Fastback]

Why are you not doing the same design for the form as you have done it in Excel?

|Route| |Vehicle|

The board is more of a GUI setup for the dispatcher. In the case of Excel, they have a "front end" worksheet (a screenshot of which is attached below) and then there is a hidden worksheet which is just two columns as I described above, linked to the various cells. That hidden worksheet is what is imported into Access and joined to the shift information table.

 

[weeblebiker]

any form you make in acces can be used to imput data back into the tables. you need to link the individual field to the table column you want the info to go into.
Maybe the thing your missing it the primary key assignment to a field that is unique to each record? access can generate primary key fields

 

[Stang70Fastback]

Well I have managed to link each field to an individual column. However, the problem is that I end up with one record with 100 columns (one for each route assignment.) This too wouldn't necessarily be a problem but I need some way of flipping the table so that I end up with two columns, rather than two rows, because that's the only format that will allow me to merge the data with another table.
In other words, I wish there was a way to link each text box to a different RECORD (row), rather than a different column, in a table.