Combine live data from two tables

[jpstroud]

Actually, the header is a bit misleading. I need to duplicate specific data from the same table, while changing only specific data. Let me explain.

I have a directory database that I maintain (note, I can NOT change the database structure - just the data) on a SQL server. I have made an access front end to facilitate reports and queries (only tool I have available to do such). This database tracks libraries, their branches, the staff, and the branch staff which I then export (using VBA) to the layout program I use to create the directory itself.

Now, it has come up recently that some libraries in this database would like to be listed in multiple locations. Either under two categories, or in one case, a shared library that would like to be listed under two systems. Due to the limits I have on the SQL database, I cannot make a duplicate of the libraries, and wouldn't want to do this as it would possibly introduce inconsistancies down the road.

The solution would have to be either code, or another table (or perhaps both) within the access frontend. Anyone have thoughts on this? If you need table structures, I can provide those. Thanks!

 

[AlexMidd]

I'm a little confused. The libraries want to be listed in two "locations". Do you mean in separate databases or two locations that are data in the database? What is the nature of the locations that are required?

Have fun! )

Alex Middleton

 

[jpstroud]

Sorry, I went into the locations in the next sentance. We have categories that libraries are filed under, and in the majority of the cases we've filed folks under one category only. I'd like to be able to have them listed twice when the information is queried.

The person who is in charge of the database uses it for other purposes as well, and duplicating the data so that it lists twice is right out (and as I said earlier, increases the likelihood of inconsistancies in the data later).

I'd prefer that the data gets inserted at the Access front end, as it gets formated and placed correctly in the final export. Manually changing the final product is possible but makes the entire export worthless. Further, I don't want to have them in a notes log elsewhere at the request of the person in charge of the project.

Further, yes.. I do find myself chanting "I love my job, I love my job, I love my job...." why do you ask?

J