creating relationship where columns rows same

[rewob65]

I am trying to get info from two databases where one shows the licence plate of the vehicle and all information in a row. On another database I have the date showing where the vehicle (column header) has been the field header.

In Excel I use a match formula to get the info in the correct format to use in Access but want to use Access in totality and cannot think how to do it?

I am quite new to using Access so any answers are useful. Thanks

 

[GingerR]

So in the second database, a vehicle license plate number is actually a field name? Sounds like you need to change the structure of this table to be normalized and in accordance with Relational Database design rules. See the links below.

The data for each item should be in a row. Also, depending on what info exactly is in the 2nd table, you might be able to combine the data together into one table. Why 2 databases? Why not one? Is one owned by someone else? Depending on your circumstances, the best thing may be to import in the data from the 2nd db and then append the data into a table structure that makes sense.


Borrowing from Aceman1, you might want to check these items:

Table Relationships Normalizing Tables Relational Database Design

Let us know more information if you need more help (including table struture, data samples). Thanks!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

two databases ... one shows the licence plate of the vehicle and all information in a row. ... another database I have the date showing where the vehicle (column header) has been the field header.

Okay, the first "database", I can understand
- LicensePlate
- Info on the vehicle

The second, "database", I am not too sure what you mean. Do you mean locations where the license plate or car has been?

Are you tracking the license plate or the vehicle? And why do you have "two" databases? Are these tables within the database?

Richard

 

[rewob65]

Yeah the other database is where the vehicle has been on each day. What I am trying to do is combine an excel spreadsheet which shows where vehicles go each day with a fuel database which works as described above. It may be that I have to change my solution.

 

[willir]

Okay...
Details on Vehicles, and where vehicles go every day can easily be done in Access.

Recording milage as read frmo the meter and fuel consumed / purchased can also be done fairly easily.

The problem for calculating statisitcs such as mpg or L/100Km or net milage is fairly advanced.

Now, to use Excel or Access....
If you have a lot of cars or what to track for a long time, I would favour using Access. If not, a spreadsheet application would work fine.

Linking to Excel spreadsheets is a pain -- anyway you can have data entered into a database frontend?