macros for access file

[razvan]

Dear All,

Need help with importing specific data from an Excel file to an Access file.
See below

Excel file

Reference Colour
10000 red
10001 green
10002 blue

Access file

Ref Colour
10000
10000
10000.01
10001
10001.03
10002
10002
10002.02


the colour should be the same for the same reference (first five numbers), no
matter what appears after the main reference e.g. .01 .02 .03 and no matter
how many times the reference appears on the access file

Thank you all in advance

 

[Guest]

Try explaining it again.
I do not see how you get from 3 records in Excel to 8 records in Access.
How are you converting text (color names) to decimal fraction?
What do you mean by 'main reference' and 'same reference'?

 

[razvan via AccessMonster.com]

Try explaining it again.
I do not see how you get from 3 records in Excel to 8 records in Access.
How are you converting text (color names) to decimal fraction?
What do you mean by 'main reference' and 'same reference'?

Dear All,

[quoted text clipped - 25 lines]

Thank you all in advance

Dear karl,
Thank you for your promp replay,
In the excel file i have all the references only ones ( arround 9000
references ) each one of them having their colour.
In the Access file the same references are repeated sometimes 5 or 6 or 10...
or more.
Some references are changing ( in the access file ) to 10000.01 or 1000.02
adding to the main reference .01, .02, .03 .
What I need to do is, in the access file ( having the same fields as Excel
file-I also can convert the Excel in to an Access file if this helps) I want
to import for each reference the colour name,no matter how many time the same
reference appears on the Acceess file, and no matter if the reference has .01
or .02 or.. to the end of it

Thank you again for your help

 

[Guest]

Maybe I am just slow today. Post more data example from your Excel, What it
looks like in Access, and how you would like it to be in Access.
--
KARL DEWEY
Build a little - Test a little

Try explaining it again.
I do not see how you get from 3 records in Excel to 8 records in Access.
How are you converting text (color names) to decimal fraction?
What do you mean by 'main reference' and 'same reference'?

Dear All,

[quoted text clipped - 25 lines]

Thank you all in advance

Dear karl,
Thank you for your promp replay,
In the excel file i have all the references only ones ( arround 9000
references ) each one of them having their colour.
In the Access file the same references are repeated sometimes 5 or 6 or 10...
or more.
Some references are changing ( in the access file ) to 10000.01 or 1000.02
adding to the main reference .01, .02, .03 .
What I need to do is, in the access file ( having the same fields as Excel
file-I also can convert the Excel in to an Access file if this helps) I want
to import for each reference the colour name,no matter how many time the same
reference appears on the Acceess file, and no matter if the reference has .01
or .02 or.. to the end of it

Thank you again for your help

 

[david12]

Try explaining it again.
I do not see how you get from 3 records in Excel to 8 records inAccess.
How are you converting text (color names) to decimal fraction?
What do you mean by 'main reference' and 'same reference'?

[quoted text clipped - 25 lines]

Thank you all in advance

Dear karl,
Thank you for your promp replay,
In the excel file i have all the references only ones ( arround 9000
references ) each one of them having their colour.
In theAccessfile the same references are repeated sometimes 5 or 6 or 10...
or more.
Some references are changing ( in theaccessfile ) to 10000.01 or 1000.02
adding to the main reference .01, .02, .03 .
What I need to do is, in theaccessfile ( having the same fields as Excel
file-I also can convert the Excel in to anAccessfile if this helps) I want
to import for each reference the colour name,no matter how many time the same
reference appears on the Acceess file, and no matter if the reference has .01
or .02 or.. to the end of it

Thank you again for your help

Your explanation is a bit difficult but I think you want to update the
Access table's Colour field based on the corresponding reference field
joined to the reference column in the Excel file.
Question: In the Access file, is the data-type for the reference field
(10000.01, etc.) a number or text?
Dave

 

[razvan via AccessMonster.com]

Dear Dave ,
The data-type for reference field is text.
Let's try to explain it different
Let's say Excel is a file with over 9000 records.Let's say movies.FIrst
starts from 10000 and each movie has a name.
Access file has few fields like Reference,Title,etc...
Each movie could have 2,3,4 or more copies wich are marked,let's say for
10000, as 10000.01 ,10000.02 etc.
The references 10000.01 10000.02 ... have the same title, of course as 10000
because they are copy of 10000.
The access file keeps record of which movie has been rented by whom:

Reference title Customer
10000 x john
10000 x mike
10000.01 x helen
10001 y bob
......
This above access file has over 300000 rows as above
So What I need to do is to complete the field ''title'' in the Access file.
The titles are available in a Access or Excel file ( whatever is easier )

Sorry for not being very clear and thank you again

 

[razvan via AccessMonster.com]

Dear Karl, ,

Sorry it's me didn't explain it clear enough.
Let's say Excel is a file with over 9000 records.Let's say movies.FIrst
starts from 10000 and each movie has a name.
Access file has few fields like Reference,Title,etc...
Each movie could have 2,3,4 or more copies wich are marked,let's say for
10000, as 10000.01 ,10000.02 etc.
The references 10000.01 10000.02 ... have the same title, of course as 10000
because they are copy of 10000.
The access file keeps record of which movie has been rented by whom:

Reference title Customer
10000 x john
10000 x mike
10000.01 x helen
10001 y bob
......
This above access file has over 300000 rows as above
So What I need to do is to complete the field ''title'' in the Access file.
The titles are available in a Access or Excel file ( whatever is easier )

Is this easier?
Sorry for not being very clear and thank you again

 

[Steve Schapel]

Razvan,

Did you see my response to you in the other thread on the similar
question? If so, did you try my suggestion? If so, what didn't you
like about it?

 

[razvan via AccessMonster.com]

Dear Steve,

Ni I didn't .Where is that?
Please direct me.
My appologies

Thank you
Razvan

 

[Steve Schapel]

Razvan,

Below I simply copy/paste from my reply to your other similar question,
where you used the example of fields named Reference/Ref and Name...

______________

Is the value in the Ref field a Text data type? Does the Reference
field have a unique index?

Try this idea, on a backup copy of your database...

First of all, make a query based on the second table. Add both fields,
and then in the Field row of a blank column in the query design grid,
enter like this:
BaseRef: CInt([Ref])

Then, make another query, which includes the first table, plus the above
query, joined on the Reference<->BaseRef fields.
Make this an Update Query (select Update from the Query menu). In the
Update To row of the Name field (2nd table), enter the equivalent of:
[NameOfFirstTable].[Name]

Run the query, should do what you want.

By the way, Name is a Reserved Word (i.e. has a special meaning) in
Access, and as such should not be used as the name of a field or control.