Tables

Resolved · Urgent Priority · Version 2003

Thu 20 Aug 2009, 17:46 Delegate Vishal said...

Vishal has attended:
Access Introduction course
Access Intermediate course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Access Advanced course

Tables

Hi,

- please explain how to import a table of data from excel into access.

- the table in question provides closing USD:EUR forex rates for each day over past year, which I need to use to convert sales numbers in a seperate table. please explain how I can set up query that links the two tables and how to multiply sales number by fx rate. i would like to do this in access rather then excel.

Mon 24 Aug 2009, 16:35 Trainer Simon said...

RE: Tables

Hi Vishal,

Thank you for your question.

In response to your first question, you use the commands ‘File, Get External Data, Import’ and then choose the appropriate Excel file. Let Access add a Primary key for you.

Follow the steps of the wizard to import the data into an Access table.

To assist me further, are the Sales figures in an existing Access table and if so what other fields are in the table?

Once the tables are linked you can then create a query that will multiply the exchange rate by the Sales figure:

1. Create a new query and add the relevent fields.
2. Click into the next available fieldname and click the Expression Builder button(looks like a wand).
3. Type the name of the new field followed by a colon(:).
4. Open the Tables folder in the left pane and add the relevant field from each table by double clicking on them.
5. Delete any <<Expr>> and put a * between the two fields for multiply.
6. Then tick the Show box.

Regards

Simon

Mon 24 Aug 2009, 20:04 Delegate Vishal said...

RE: Tables

Thank you for your response. I will try this on wednesday when back in office.

The sales figures are in an existing access table which has numerous other fields detailing various aspects of sale (client, amount, currency, settlement, product etc.).

I do not see how I can link the forex table to the sales table. The only common field is the date, but they do not have same string of values. The dates on the forex table are continuous (ie every day this year) whereas the dates on the sales table are broken (and only occur when transactions are made and there may be multiple on any given day). Do you know how I could link the tables?

Mon 24 Aug 2009, 20:04 Delegate Vishal said...

RE: Tables

Wed 2 Sep 2009, 13:33 Trainer Simon said...

RE: Tables

Hi Vishal,

Thank you for your response.

Without spending time looking at the tables it is hard to answer your question.

The only advice I can give you is what you have stated. You must have two fields that share the same data.

The only alternative is to add a calculated field in a query that multiplied the Sales total by the exchange rate.

Create a query and add the relevant fields. Enter any criteria to pick certain types of sale E.g. particular currency. Add a new field in the next blank field as follows:

ExchangeAmount:[SalesTotal)*1.06
(substitute the fieldname and relevant exchange rate

I hope this helps you.

Regards

Simon

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Access tip:

Dsum

You can create percentage values based on individual products / items by using a Dsum function (used with a grouped query)

[Each value column] / Dsum[field:total for the column needed to be calculated],[tablename]

View all Access hints and tips