Getting data from another database in your database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I'm really new to Access and need help understanding how to design my
database. There is an existing database the client owns that I want to pull
information from and automatically update in my database.

In the client database there exists a query that lists all software the
client owns and how many installations of it are out there (using a duplicate
record). I want to pull this information into my database so that I can add
another column with an ID field in it. This ID field will match a field in
another table in my database and will be used to run a VB script to install
software. Right now I'm just manually copying the software information from
the client table, but I would like to set it up so if they make a change to
the software list it will automatically show up in my table, and I can add
the ID to it. I know this is REALLY confusing. It's hard to explain, but if
anyone can offer any assistance on how the best way to do this is, please let
me know! Thanks.
 
Kristina said:
Hi, I'm really new to Access and need help understanding how to
design my database. There is an existing database the client owns
that I want to pull information from and automatically update in my
database.

In the client database there exists a query that lists all software
the client owns and how many installations of it are out there (using
a duplicate record). I want to pull this information into my
database so that I can add another column with an ID field in it.
This ID field will match a field in another table in my database and
will be used to run a VB script to install software. Right now I'm
just manually copying the software information from the client table,
but I would like to set it up so if they make a change to the
software list it will automatically show up in my table, and I can
add the ID to it. I know this is REALLY confusing. It's hard to
explain, but if anyone can offer any assistance on how the best way
to do this is, please let me know! Thanks.
Click to expand...

If I'm understanding you correctly, I think your best bet is to do the
following:

1. Create a linked table in your database that points to the Software
table in your client's database. I'll suppose for the moment that you
can go ahead and name this linked table "Software", too. (I recognize
that you referred to "a query that lists all software the client owns
and how many installations of it are out there", and that you may have
to link to the tables that compose that query and recreate that query in
your database. For simplicity, though, I'll assume you just need to
link to a single table.)

2. Create a new table in your database with two fields in it. One is
the ID field that you want to use to link to that other table in your
database, and the other field is one that you will set to match the
primary key of a record in the Software table. Thus, this table serves
as a link between the Software table and that other table of yours.
Let's call this table "SoftwareLinks".

3. Create a query that joins Software to SoftwareLinks on the key field
they have in common, using a left join so that all records from Software
are incuded and only the matching records from SoftwareLinks. Include
all the fields from Software, and the ID field from SoftwareLinks. Call
this query "qrySoftwareLinks".

4. You can open qrySoftwareLinks to add the ID field for any record that
doesn't have one. The ID field you enter will be stored in
SoftwareLinks.

5. You can also use qrySoftwareLinks wherever you would have used the
modified copy of the Software table table you originally proposed.
 
Dirk,

Thanks so much for explaining that so well. I'm surprised you managed to
understand what I was trying to do as well as you did! I did end up having
to create an extra query in my database to replicate the one in the client
database, but I'm down to working on the last piece of it...being able to
input the ID in the qrySoftwareLinks. If I add a column for it, it doesn't
let me update it. The following is the code I am using for that query.

SELECT [Software-AMIS].AIMSAppCount,
[Software-Plantation].PlantationAppCount, [Software-AMIS].AddRmvName
FROM [Software-AMIS]
LEFT JOIN [Software-Plantation]
ON [Software-AMIS].AddRmvName = [Software-Plantation].AddRmvName
UNION SELECT [Software-AMIS].AIMSAppCount,
[Software-Plantation].PlantationAppCount, [Software-Plantation].AddRmvName
FROM [Software-AMIS]
RIGHT JOIN [Software-Plantation]
ON [Software-AMIS].AddRmvName = [Software-Plantation].AddRmvName;

Thanks! Kristina
 
Kristina said:
Dirk,

Thanks so much for explaining that so well. I'm surprised you
managed to understand what I was trying to do as well as you did! I
did end up having to create an extra query in my database to
replicate the one in the client database, but I'm down to working on
the last piece of it...being able to input the ID in the
qrySoftwareLinks. If I add a column for it, it doesn't let me update
it. The following is the code I am using for that query.

SELECT [Software-AMIS].AIMSAppCount,
[Software-Plantation].PlantationAppCount, [Software-AMIS].AddRmvName
FROM [Software-AMIS]
LEFT JOIN [Software-Plantation]
ON [Software-AMIS].AddRmvName = [Software-Plantation].AddRmvName
UNION SELECT [Software-AMIS].AIMSAppCount,
[Software-Plantation].PlantationAppCount,
[Software-Plantation].AddRmvName FROM [Software-AMIS]
RIGHT JOIN [Software-Plantation]
ON [Software-AMIS].AddRmvName = [Software-Plantation].AddRmvName;
Click to expand...

Uh oh. We have a problem. No UNION query is ever going to be
updatable. I don't know enough about the structure of the data to know
whether you can change things around in the underlying structure to
eliminate the need to do a union query here. Failing that, I think
you'll have to break this up into two separate queries -- one to do an
inner join and one to do a restricted outer join (where one side's join
field is Null) so as to pick up the records that the inner join
misses -- and process each separately.
 
I wondered if that might have been the problem. Let me see if I can describe
my database because I've been struggling with the best way to create the
tables and queries to get the results I need.

I'm linking to the client's table (SoftwareList) which contains a list of
computers and every application (AddRmvName) on it. I then have an append
query (Create Software-AIMS Table) which creates a list of the AddRmvName and
the number of duplications (AIMSAppCount) that appear in the SoftwareList
table, which gives me a table (Software-AMIS) of all software apps and the
number of instances in the company. I repeat this process with the other
sites in the company. Using the Software-<location> tables I create a query
(Data for Software Table) using the code given to you before. Then I have an
append query (Create Software Table), which appends a table (Software) with
the AddRmvName and AppCount. So in the end I have a table that contains the
AddRmvName of all software from the various locations and columns containing
the instances at each location.

Now where I'm running into problems is that ID field I need to manually add
an entry for each application to. I can add it to the Software table, which
is where it needs to be but using the append queries my AppCounts will never
update, and I would like a way for them to without losing the ID information
I add.

What do you think?

Dirk Goldgar said:
Kristina said:
Dirk,

Thanks so much for explaining that so well. I'm surprised you
managed to understand what I was trying to do as well as you did! I
did end up having to create an extra query in my database to
replicate the one in the client database, but I'm down to working on
the last piece of it...being able to input the ID in the
qrySoftwareLinks. If I add a column for it, it doesn't let me update
it. The following is the code I am using for that query.

SELECT [Software-AMIS].AIMSAppCount,
[Software-Plantation].PlantationAppCount, [Software-AMIS].AddRmvName
FROM [Software-AMIS]
LEFT JOIN [Software-Plantation]
ON [Software-AMIS].AddRmvName = [Software-Plantation].AddRmvName
UNION SELECT [Software-AMIS].AIMSAppCount,
[Software-Plantation].PlantationAppCount,
[Software-Plantation].AddRmvName FROM [Software-AMIS]
RIGHT JOIN [Software-Plantation]
ON [Software-AMIS].AddRmvName = [Software-Plantation].AddRmvName;
Click to expand...

Uh oh. We have a problem. No UNION query is ever going to be
updatable. I don't know enough about the structure of the data to know
whether you can change things around in the underlying structure to
eliminate the need to do a union query here. Failing that, I think
you'll have to break this up into two separate queries -- one to do an
inner join and one to do a restricted outer join (where one side's join
field is Null) so as to pick up the records that the inner join
misses -- and process each separately.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Kristina said:
I wondered if that might have been the problem. Let me see if I can
describe my database because I've been struggling with the best way
to create the tables and queries to get the results I need.

I'm linking to the client's table (SoftwareList) which contains a
list of computers and every application (AddRmvName) on it. I then
have an append query (Create Software-AIMS Table) which creates a
list of the AddRmvName and the number of duplications (AIMSAppCount)
that appear in the SoftwareList table, which gives me a table
(Software-AMIS) of all software apps and the number of instances in
the company. I repeat this process with the other sites in the
company. Using the Software-<location> tables I create a query (Data
for Software Table) using the code given to you before. Then I have
an append query (Create Software Table), which appends a table
(Software) with the AddRmvName and AppCount. So in the end I have a
table that contains the AddRmvName of all software from the various
locations and columns containing the instances at each location.

Now where I'm running into problems is that ID field I need to
manually add an entry for each application to. I can add it to the
Software table, which is where it needs to be but using the append
queries my AppCounts will never update, and I would like a way for
them to without losing the ID information I add.

What do you think?
Click to expand...

I can't claim to have a very good picture of the data, and I may ask you
to supply field lists for all the tables, but I think you are probably
creating problems for yourself here:
[...] I repeat this process with the other sites in the
company. Using the Software-<location> tables I create a query
[...]
Click to expand...

Do I understand you correctly, that you are creating several tables with
the same structure, where each table contains data for a different "site
in the company"? This unnormalized structure is going to be cumbersome
to work with -- it would be much better to have a single table in which
the "site" is just another field in each record. Is this the way the
data is starting out, on the client's side? You referred to a
SoftwareList table -- is that actually multiple tables, one for each
site? I'm confused.
 
The client's database is denormalized. Each site has it's own database, so I
had to create a link to each of them. I then copied my existing queries and
tables for each site and changed the variables to the current site, then fed
it into a final database table. Everything is working, but the one piece I
wish was working is that the Application Count columns in the final table
would update, but because it's using an append table query it only adds
records that don't exist and doesn't update the existing ones.

Dirk Goldgar said:
Kristina said:
I wondered if that might have been the problem. Let me see if I can
describe my database because I've been struggling with the best way
to create the tables and queries to get the results I need.

I'm linking to the client's table (SoftwareList) which contains a
list of computers and every application (AddRmvName) on it. I then
have an append query (Create Software-AIMS Table) which creates a
list of the AddRmvName and the number of duplications (AIMSAppCount)
that appear in the SoftwareList table, which gives me a table
(Software-AMIS) of all software apps and the number of instances in
the company. I repeat this process with the other sites in the
company. Using the Software-<location> tables I create a query (Data
for Software Table) using the code given to you before. Then I have
an append query (Create Software Table), which appends a table
(Software) with the AddRmvName and AppCount. So in the end I have a
table that contains the AddRmvName of all software from the various
locations and columns containing the instances at each location.

Now where I'm running into problems is that ID field I need to
manually add an entry for each application to. I can add it to the
Software table, which is where it needs to be but using the append
queries my AppCounts will never update, and I would like a way for
them to without losing the ID information I add.

What do you think?
Click to expand...

I can't claim to have a very good picture of the data, and I may ask you
to supply field lists for all the tables, but I think you are probably
creating problems for yourself here:
[...] I repeat this process with the other sites in the
company. Using the Software-<location> tables I create a query
[...]
Click to expand...

Do I understand you correctly, that you are creating several tables with
the same structure, where each table contains data for a different "site
in the company"? This unnormalized structure is going to be cumbersome
to work with -- it would be much better to have a single table in which
the "site" is just another field in each record. Is this the way the
data is starting out, on the client's side? You referred to a
SoftwareList table -- is that actually multiple tables, one for each
site? I'm confused.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Kristina said:
The client's database is denormalized. Each site has it's own
database, so I had to create a link to each of them. I then copied
my existing queries and tables for each site and changed the
variables to the current site, then fed it into a final database
table. Everything is working, but the one piece I wish was working
is that the Application Count columns in the final table would
update, but because it's using an append table query it only adds
records that don't exist and doesn't update the existing ones.
Click to expand...

Argh! That makes it very hard. I think what you're going to have to do
is add extra logic to the data-collection step, so that you don't
replace records nor append records wholesale, but rather collect from
each site the necessary information to add, delete, or update the
records you already have. In other words, you would have your own table
with IDs and counts as you have previously assigned them, and you would
run a process daily (or whenever) that links to each site in turn,
extracts, collates, and summarizes the information from that site, and
updates your table, updating the counts and adding or deleting records
if necessary.
 
Back
Top