How to make a query of two tables (1 linked to excel)

[Mauro via AccessMonster.com]

Hi,

Is there a way to make a query of two tables when one is an Excel linked
table and the other is a sinlge table?
In the linked table I have this columns:
PO Number
Supplier
PO Ammount
PO Description

and in the second table I have:
Invoice N°
Delivery options
Invoice Ammount
etc.. etc..

If I put in my second table a column with PO Number(I guess is the way to
query both tables) How does it get updated whith new orders after I update
my linked table?

I really appreciate your comments..

 

[John Vinson]

Hi,

Is there a way to make a query of two tables when one is an Excel linked
table and the other is a sinlge table?
In the linked table I have this columns:
PO Number
Supplier
PO Ammount
PO Description

and in the second table I have:
Invoice N°
Delivery options
Invoice Ammount
etc.. etc..

If I put in my second table a column with PO Number(I guess is the way to
query both tables) How does it get updated whith new orders after I update
my linked table?

I really appreciate your comments..

How are the tables related, in a real-life business sense? Does each
PO in the Excel spreadsheet generate an invoice; or might it generate
multiple invoices; or might an invoice cover several POs?

You might need an Append query to create new invoices based on a PO,
or an Update query to link a given PO to the invoice of that PO,
updating the amount field - I'm not certain what business problem
you're trying to solve so I'm not sure what to advise!

John W. Vinson[MVP]

 

[Mauro via AccessMonster.com]

Hi John,

This is a customs clearance control system. And as you stated, tables are
related in a real-life business sense. I mean, for each PO I can have
multiple invoices an then for each invoice I need to control a lot of more
information related to each invoice.
At the beginning I had everything under control, controling my database by
the invoice number.. but know I found out that it would be more important
to do it by PO since I can import once a week PO information from another
system (and database), so I don't have to re-type all the info related to
the PO.

I appreciate your opinion and recommendation.

Mauro.

 

[John Vinson]

Hi John,

This is a customs clearance control system. And as you stated, tables are
related in a real-life business sense. I mean, for each PO I can have
multiple invoices an then for each invoice I need to control a lot of more
information related to each invoice.
At the beginning I had everything under control, controling my database by
the invoice number.. but know I found out that it would be more important
to do it by PO since I can import once a week PO information from another
system (and database), so I don't have to re-type all the info related to
the PO.

Well, first off - don't store any data redundantly in both the Invoice
table and the PO table. If you can look up PO information related to
an invoice using a Query, do so.

Then, I'd suggest storing a PO Number in the Invoices table, as a
foreign key; use the Relationships window to ensure that you can't
create an invoice unless there is a valid PO number for it.

Finally, do your data entry using a Form based on the PO linked table,
with a Subform based on the Invoices table. Using the PO Number as the
master/child link field will ensure that the invoices are linked to
the correct PO.

John W. Vinson[MVP]

 

[Mauro via AccessMonster.com]

Hi John,

Thanks for your reply.. it's clarifiying my way approaching..

...Well, first off - don't store any data redundantly in both the Invoice
table and the PO table. If you can look up PO information related to
an invoice using a Query, do so.

I don't plan to stored redundant data in both tables.. One of my doubts was
how can I make a query of a "normal" table and an "excel linked" table.. if
there a way to do it?

...Then, I'd suggest storing a PO Number in the Invoices table, as a
foreign key; use the Relationships window to ensure that you can't
create an invoice unless there is a valid PO number for it.

I guess it is related to the statement above.. I'm figuring out how to do
it.. I'm not an expert DB or Access user.. what I have done so far, has
been done only with my poor understanding of access..
Everything was working fine when I controlled the DB by the invoice number,
but now it's better to do it by Po so I can use the application with other
projects which primary will be always the PO.

I would appreciate if you can explain both of above issues a little bit.

Thanks a lot.

Mauro.

 

[John Vinson]

Hi John,

Thanks for your reply.. it's clarifiying my way approaching..

..Well, first off - don't store any data redundantly in both the Invoice
table and the PO table. If you can look up PO information related to
an invoice using a Query, do so.

I don't plan to stored redundant data in both tables.. One of my doubts was
how can I make a query of a "normal" table and an "excel linked" table.. if
there a way to do it?

Yes, exactly the same way you would create any other query. A linked
Excel table has some limitations, but in the main it's just a table;
you can add it to the Query window, add your Invoices table to the PO
window, drag a PO number from the linked table to the Invoices table,
select whatever fields you like.

..Then, I'd suggest storing a PO Number in the Invoices table, as a
foreign key; use the Relationships window to ensure that you can't
create an invoice unless there is a valid PO number for it.

I guess it is related to the statement above.. I'm figuring out how to do
it.. I'm not an expert DB or Access user.. what I have done so far, has
been done only with my poor understanding of access..
Everything was working fine when I controlled the DB by the invoice number,
but now it's better to do it by Po so I can use the application with other
projects which primary will be always the PO.

I would appreciate if you can explain both of above issues a little bit.

I cannot, because I do not know or understand your business or what
you mean by "controlling" the db. PO numbers and Invoice numbers are
just data - data that can be used as links, but neither one "controls"
the database; I'm not even clear what you mean by that statement!

You may run into some problems enforcing relational integrity if you
are forced to always use Excel as the primary storage medium for your
PO's. Excel is *NOT* a relational database program, and knows nothing
about relational integrity; you cannot, for instance, create a
permanent relationship between the linked spreadsheet and your invoice
table to ensure that you only enter valid PO's. Is there any
possiblity of *importing* the spreadsheet, or (faint hope here...)
having an Access or SQL/Server table as the data repository for PO's,
and exporting the data to Excel as needed?

John W. Vinson[MVP]

 

[Mauro via AccessMonster.com]

Thanks John,

Finally I got what I wanted..
I need to import "periodically" the excel file because it contains the data
from the Po system, so as PO are loaded to that system weekly, I need to
have that information into my "import and customs clearance system" that
i'm trying to develop.

With "controlling" I meant that my primary key would be "invoices" instead
of "PO's", but then in that case I had to re-type PO's info.. So I decided
to import such data from the PO's system.

What I did was:
1.- Created a linked excel file from my PO's system
2.- Created a query based in that linked file named "Purchase Orders"
(which is my form data entry base)
3.- Created a table named "invoices" with a look-up to my query "Purchase
Orders".
4.- Created a sub-form based in this table ("invoices")

Now, everything is working great. I really appreciate your inputs.

Regards,

Mauro.

 

[Mauro via AccessMonster.com]

One more thing.

Is there a way to split the data base with linked excel file? I'm having
trouble giving other users access through the network. It says that my
"linked" excel file is being used by another user, so I can't get into the
database..

Any tip?

Thanks.

Mauro

 

[John Vinson]

One more thing.

Is there a way to split the data base with linked excel file? I'm having
trouble giving other users access through the network. It says that my
"linked" excel file is being used by another user, so I can't get into the
database..

Linked Excel spreadsheets cannot be shared, IIRC. You'll need to
import the Excel into an Access table if you want to share it.

John W. Vinson[MVP]

 

[Mauro P via AccessMonster.com]

John,

How can I do to import the table periodically? Can I make a single user to
import the table by clicking a button as a procedure? Could you please copy
here what would be the code to do this? (I have no expertise with codes and
that stuff..)

Thanks again John,

 

[Mauro P via AccessMonster.com]

John,

While I was reading other posts finding some advices to solve my problem, I
read that its possible to import and update data to a table from Oracle
Data Base.. My linked excel file is an export from an Oracle Data Base (PO
system). So if that is true, and I import the data directly from Oracle,
will I have the same problem that I having with the Excel file? I mean,
would it affect the sharing?

Mauro.