Importing Excel Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob Roy CC - 28 Oct 2007 15:56 GMT

In my database I have two tables (Jobs & Files). I have a form (Jobs) with a
Subform (Files) where most of the data is entered. I have a one to many
relationship (Jobs(1), File(many)) using the Primary Key in "Jobs"
(JobNumber) as a Foreign Key in "Files". Some of the data needs to come from
an Excel spreadsheet. I have imported the data into the Files Table and it
shows up fine, my problem is that my Jobs Table does not get updated with the
new Job Numbers and when I go into My Jobs Form to edit the new files I can't
bring them up.
How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message

Douglas J. Steele - 28 Oct 2007 16:06 GMT

Having a relationship between two tables does nothing to populate the
tables.

If you've imported new data into Files and it represents JobNumbers that
don't exist in the Jobs table, you'll have to write an Append query to add
those JobNumbers to the Jobs table.

If you create a query along the lines of:

SELECT DISTINCT JobNumber
FROM Files LEFT JOIN Jobs
ON Files.JobNumber = Jobs.JobNumber
WHERE Jobs.JobNumber IS NULL

you'll get a list of those JobNumbers which are missing from the Jobs table.
Use that query as the basis for your Append query.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> In my database I have two tables (Jobs & Files). I have a form (Jobs) with
> a
[quoted text clipped - 10 lines]
> bring them up.
> How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message

Rob Roy CC - 28 Oct 2007 16:23 GMT

Thanks for responding Douglas, I am somewhat new to this and I have never
written an Append query, how do I tell Access that it is an Append query or
better still how do I write period.
Thanks in advance, Rob

> Having a relationship between two tables does nothing to populate the
> tables.
[quoted text clipped - 27 lines]
> > bring them up.
> > How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message

Douglas J. Steele - 28 Oct 2007 18:43 GMT

The SQL would be something like:

INSERT INTO Jobs (JobNumber)
SELECT DISTINCT JobNumber
FROM Files LEFT JOIN Jobs
ON Files.JobNumber = Jobs.JobNumber
WHERE Jobs.JobNumber IS NULL

If you're using the graphical query builder, you can change a query into an
Append query through the Query menu when the query's open in Design view.

I'd spend some time making sure you understand queries: they're a critical
building block for database applications.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks for responding Douglas, I am somewhat new to this and I have never
> written an Append query, how do I tell Access that it is an Append query
[quoted text clipped - 40 lines]
>> > bring them up.
>> > How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message

Rob Roy CC - 28 Oct 2007 19:55 GMT

Hi Douglas, Thank you, this did the trick! I wil definitely learn more about
Queries. Thanks
Rob

> The SQL would be something like:
>
[quoted text clipped - 54 lines]
> >> > bring them up.
> >> > How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message

Peter Hibbs - 28 Oct 2007 16:55 GMT

If this is a 'one off' operation try using the Excel to Access
Converter Utility program at :-

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Excel%20to%20Ac
cess%20Converter%20Utility%20program'

HTH

Peter Hibbs.

>In my database I have two tables (Jobs & Files). I have a form (Jobs) with a
>Subform (Files) where most of the data is entered. I have a one to many
[quoted text clipped - 5 lines]
>bring them up.
>How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message

Rob Roy CC - 28 Oct 2007 17:24 GMT

Thanks Peter, Tried the link but it didn't work. I got there and found the
file by going to his site and then google search his site for the file. I
have downloaded and will try it shortly then I will let you know if it solves
my problem. The error in the link was that it is"otherlibraries.asp" as
opposed to "otherdownload.asp". Thanks for the help.
Rob

> If this is a 'one off' operation try using the Excel to Access
> Converter Utility program at :-
[quoted text clipped - 14 lines]
> >bring them up.
> >How can I get my Jobs Table to know about the new Jobs/Files?

Reply to this Message