A import problem that I need to tackle (1 Viewer)

Y

Yecats131

Registered User.

Local time

Today, 13:02

Joined

Apr 22, 2008

Messages

63

• Apr 24, 2008

• #1

Hello!

So, I'm currently teaching myself Access as I go along. I have basic VB knowledge as well. The problem that I need to tackle is the following:

I have a table that contains several fields... The 3 that have to deal with this are: Req#, Status, and Status date.

Req number: primary Key
Status: Will Change
status date: Will Change

So, I have a spread sheet from another program that is ran once a week that contains the req# and new status information for those particular requisitions. Rather then going into the table one by one and changing the status and date myself... I want to know if there is a way to set it up to have the spreadsheet with the new information imported into access and automatically change the status information over.


Please let me know if I'm not making any sense... :0)

Thanks!

 

KeithG

AWF VIP

Local time

Today, 13:02

Joined

Mar 23, 2006

Messages

2,592

• Apr 24, 2008

• #2

Import the spreadsheet then run an update query to update the status and date.

 

Y

Yecats131

Registered User.

Local time

Today, 13:02

Joined

Apr 22, 2008

Messages

63

• Apr 24, 2008

• #3

In noob speak please

 

Villarreal68

Registered User.

Local time

Today, 13:02

Joined

Feb 15, 2007

Messages

133

• Apr 24, 2008

• #4

This is the best I an offer! Sorry!

How to link to an Excel Workbook: http://office.microsoft.com/en-us/access/HA012194191033.aspx?pid=CH101759701033

How Update Queries work: http://office.microsoft.com/en-us/access/HA100765271033.aspx?pid=CH100645771033

Hope this helps!

 

Y

Yecats131

Registered User.

Local time

Today, 13:02

Joined

Apr 22, 2008

Messages

63

• Apr 25, 2008

• #5

Actually... this helps a lot.

Thank you

 

Y

Yecats131

Registered User.

Local time

Today, 13:02

Joined

Apr 22, 2008

Messages

63

• Apr 25, 2008

• #6

Okay, so I've ready over the tutorials and Help sites that were provided to me. It looks like a Update Quary only allows you to update information built in.... which doesn't help.

(Replace ___ with ____ if x condition is met)

What I have is a spreadsheet with a list of requisition numbers who's status information needs to be updated.

When I import an excel spreadsheet directly, does it locate the Primary Key (Requisition number) and update the information accordingly?

 

KeithG

AWF VIP

Local time

Today, 13:02

Joined

Mar 23, 2006

Messages

2,592

• Apr 25, 2008

• #7

What do you mean by built in? An update query will update infomation in a field.

 

G37Sam

Registered User.

Local time

Tomorrow, 00:02

Joined

Apr 23, 2008

Messages

454

• Apr 25, 2008

• #8

Hello,

you can use the Docmd.transferspreadsheet method, this will transfer contents of a spreadsheet into a table, you can then use a query to update whatever data you need from another table

regards,
Samer

 

O

odin1701

Registered User.

Local time

Today, 14:02

Joined

Dec 6, 2006

Messages

526

• Apr 25, 2008

• #9

Yecats131 said:

Okay, so I've ready over the tutorials and Help sites that were provided to me. It looks like a Update Quary only allows you to update information built in.... which doesn't help.

(Replace ___ with ____ if x condition is met)

What I have is a spreadsheet with a list of requisition numbers who's status information needs to be updated.

When I import an excel spreadsheet directly, does it locate the Primary Key (Requisition number) and update the information accordingly?

Click to expand...

If you tell it to.

You need to have a condition put on the primary key.

So if it's the Req#, then you just need to have the update query match the Req# from your temporary import table to the main table, then update the status field from the temporary import table.

The query will only change data where the two Req#'s are equal.