Importing Excel Data

[Guest]

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?

 

[Douglas J. Steele]

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.

 

[Guest]

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

 

[Peter Hibbs]

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

http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'

HTH

Peter Hibbs.

 

[Guest]

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

 

[Douglas J. Steele]

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.

 

[Guest]

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