New to Access

[nhylan]

Hi All,



Just to give you a little background, I work in finance and I have absolutely zero programming or tech background but I'm trying to teach myself how to use access.

What I've done so far on this project is built a database that holds all our trade data from our client in the clients original format. I've created another linked excel table that is linked to a daily trade activity report from the client. A query then checks the new activity based on a unique ID against the past activity and shows only activity we haven't seen before and then I made another query to add that new activity to the original master file.

What I would like to be able to do is sort of "translate" that data our client is sending us into a format we can use in our software. Typically when I do this in excel it involves a couple vlookup ref. tables which show our specific ID's and then I end up changing the date formatting via a "text" formula. Is there a way to do this in access? Please let me know if I can better explain something, as I said I have zero background in this stuff but I want to learn!
Thanks,
Nate

[rpeare]

Anything you can do in Excel you can do in Access, particularly if you're talking about VB.

If you can give an example of your starting data, what the conversion tables look like and what you want your final product to look like we can give you direction.

It may be as simple as creating tables that cross reference your internal codes with the external codes.

[nhylan]

Anything you can do in Excel you can do in Access, particularly if you're talking about VB.

If you can give an example of your starting data, what the conversion tables look like and what you want your final product to look like we can give you direction.

It may be as simple as creating tables that cross reference your internal codes with the external codes.

Hello,

thanks for getting back to me.

The below is an example of the columns on the clients data (all specific and sensitive data has been removed.

Head Account Number	BBH Transaction Reference Number	FX Contract Number	FX Cancel Date	FX Currency Bought	FX Currency Bought Net Settlement Amount	FX Currency Sold	FX Currency Sold Net Settlement Amount	FX Deal Rate	FX Trade Date	FX Settlement Date
9999999	B99999SPOT	B99999		USD	999.994	TWD	99.81	29.87	3/26/2013	3/27/2013

I need to get this data into this format:

Fund: ABBR: SPOT OR FORWARD? BROKER: TRADE DATE: SETTLE DATE: CURRENCY BOUGHT: CURRENCY SOLD: AMT BOUGHT: AMT SOLD: CLIENT REF#: BRS #: 1234 123 "f" or "s" 901 20130313 20130313 USD TWD 99.994 99.81 B99999 file To do this in excel I would have set up a ref table that would translate the column "Head account number" from the client data into the "fund" four digit in the second part. ABBR: I would use a "mid" formula as this is always the final three digits on the data in "Fund" Spot or Forward: I also use mid on this, it pulls the 7th digit of "bbh transaction reference" which will either be "s" or "f" Broker in this case will always be 99999 Currency bought and sold is self explanatory The dates need to be translated into a "yyyymmdd" format Client ref = "FX contract number" in client data BRS #: will always be "file" I hope that is clear, I know its a lot. I'm really looking forward to learning about this. Thanks in advance. Nwh

[rpeare]

Ok, you will need a table with the head account number and the fund, everything else you can do with manipulation

for abbr left([fund], 3) for the first three, right([fund], 3) for the last three characters of the value
S or F is mid([fieldname], <starting position>, <length>)

to change the date from a date to text field format([datefield], "YYYYMMDD")

[nhylan]

Ok, you will need a table with the head account number and the fund, everything else you can do with manipulation

for abbr left([fund], 3) for the first three, right([fund], 3) for the last three characters of the value
S or F is mid([fieldname], <starting position>, <length>)

to change the date from a date to text field format([datefield], "YYYYMMDD")

Thanks!

can this be built into a quiry? if so whats the easier way to do it? Also one last question, does the dlookup function exactly the same as the excel vlookup formula?

[rpeare]

Do not use the dlookup function on a query if you can possibly avoid it. IN your case you will have two tables. One with your original data, and one with the head account number and internal fund, when you create your query you link your two tables together through the head account number then add the internal fund number from your second table to the query. No dlookup needed.