Access Assistance

[Jator]

I've been using Access for a few years now and have been able to figure out how to do most of the things I want it to do, but there is one that has dogged me for quite some time now.

I want to have 2 tables, one with info, say an item number, description and a price. I want the other table (customer) to be a table that has a form on top of it where you could put say customer information, then when you type in the item number, it automatically inserts the description and price from the info table into fields in the customer table.

I know how to link tables, and have setup a link between the 2 tables with the link on the item number being the linked item. I can get a query to work, but the problem is is that the query can't store the data back into the customer table.

Any suggestions would be helpful.

Thanks,

Jay

 

[EagleKeeper]

Use VBA code to query the proper record in the second table and update the needed fields using the data in the form.

 

[Helpless]

>'ve been using Access for a few years now and have been able to figure out how to do most of the things<

...then help me with my simple Access question

 

[EagleKeeper]

Helpless - Check your thread

 

[theknight571]

Do you just want to display the item's details on the Customer form, or do you actually want to store that information in the customer table?

- TK

 

[Jator]

Store the data in the customer table.

 

[Jator]

Bump

 

[Helpless]

All my data are in the left hand column called, 'Items.&quot; After that, I have a dozen of so columns to the right with numerical data on volume, price per unit, dollar sales, etc, etc...So I would like it to search the 1st column (Items with text description) and only display the info for the ones with the acronyms I need, like FFR, L/F, etc, etc...

I'll draw a little picture

-------------------------------------------------------------
ITEMS | Volume of Sales | ETC |
_____________________________________________________________

+GBCHS RFG MZRL xxxx xxxx
+SRRNT RFG CHDR FFR xxxx xxxx
+PKCHS RFG PMSN SHRD xxxx xxxx
+KRAFT RFG CHDR SHRD xxxx xxxx
+KRAFT RFG CHDR SHRD xxxx xxxx
--------------------------------------------------------------

So I would like to import a table like this from excel, do a query and extract lines like #2 with FFR and all the data to the right of it...seems like just a glorified sort command, but can;t figure it out...all the rest of the lines above, without FFR, are junk to me, as I have them included in the aggregate database...just want to extract the FFR line, per say.

 

[Helpless]

Okay, I am close...need some hlp, though...If I import the data, do a select query...then click the little pencil/triangle &quot;View&quot; icon, I see where I can enter the criteria parameters...I tried it a *bazillion* times and it only showed one empty cell....then I tried to use the criteria for a different column (say field 2) and used &quot;NA&quot; since some cells say NA in the table in the column...it worked...listed over 11,000 lines in the table HOWEVER, I still can't get it to sort through the first column and extract the lines with the acronyms I place in the &quot;criteria&quot; section!!!


...could this be because it only looks at the first word? the lines, as seen above, sometimes have 5-6 different words in there, so maybe it only checks the first? Any work arounds, ideas?

 

[Helpless]

okay, sorry to keep posting so much, but I am soooo close...I got the items to sort for &quot;FRR&quot; only buy using &quot;*FFR*&quot; as the criteria, so I guess it does only look at the first word. Problem is, some of the other words have some of the letter combinations in there, like the &quot;lite&quot; acronym LT....so I sort of want it to pull only exact matchs for LT, but can;t get it to work without &quot;*LT*


...close, but no cigar...I could work around it and go line by line and see if it pulled any bogus lines out...but have about 100+ spreadsheets and many have well over 30K in lines




[EDIT] Here's line that shows what I am talking about:

ID ITEM Field2

1030 +WDFFR RCE SNCKR CRK PCR $1xxxxx.xx

it has FFR in the first word, so it listed it within my query...I need only exact matches, but unable to just use exact &quot;FFR&quot; ..have to use &quot;*FFR*&quot; to get any data to appear in the table...any ideas?


I am thinking that it scans the first letter in the cell and since there are multiple words in one cell, I have to use the wildcards, it seems...

 

[theknight571]

Jator,

Unless you need it to be done &quot;real time&quot;, the simplest way to do this would be to create an &quot;update&quot; query, where you match on the key and use the fields form the source table to &quot;update&quot; the fields in the target (customer) table.

If you needed it to be done &quot;real time&quot; I'd have to play around a little to see if I could get it to happen automatically. (I have an idea, but I'd have to test it before presenting it here.)

BTW...If you don't mind me asking...Why do you want to duplicate the data like this?

- TK