• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

MS Access HELP!!!!!!!!!. How do I...

H

Helpless

Banned
Okay, I am trying to import an excel spreadhseet, then I want Access to locate the lines of data that contain things like FFR, S/F, NSW, etc and seperate them into a new table to export back into excel...everyone tells me it's so easy and takes all but a minute, but after an hour and a dozen smokes, I give up...I was close, but it deleted the entries instead of copying them to a new table...help! 🙂


Could someone walk me through this "easy" task? 🙂


...just need to 1.) Import spreadsheet 2.) have it find the entries that contain XXXX letter combinations 3.) extract those cells 4.) create a new sheet with those cells

make sense? going to smoke, but await any help you may give *helpless*
 
..okay, I'll go smoke another one..I was close...imported the data...made a "Select Query"...went to the little pencil, triangle icon thing 😉 sent up the "Criteria" for the 1st field like "FFR," but when I hit "View," it showed only one empty cell...


 
Modify your query to use an OR statement combination to pickup all the options you wish.
Then copy the query output and paste into your spreadsheet
 
thanks, but I only used one entry in the "criteria" section and it still didn't show any data 🙁 ...but I'll use the "or" line there when I finally get one to work 😉 thanks, sir..
 
Welcome

Am training a son-in-law (to be) on Access and Forms. Both of us are learning as we go.
 
I am not sure I fully understand what your data looks like. Are these acronyms contained in one field (ie column) or a seperate field for each one? In other words, if an item (or row) had the three codes FFR, L/F, and S/F would they all be in one field or three fields? If they are in one field, searching for FFR will only work if FFR is the only item for that row. You could try using the wildcard character to search for any rows containing FFR anywhere in that field. I think for Access the wildcard character is eitehr % or *.
 
>Are these acronyms contained in one field (ie column) or a seperate field for each one<

Yea, all 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.
 
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?
 
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?
 
If you are looking for FFR as a complete &quot;word&quot;...try putting spaces before and after the FFR (ie &quot;* FFR *&quot😉. Only problem with this is if its the first word or last word in the field because there may or may not be a space before and after the word.

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

Without the *, it will start checking at the begining of the line, so &quot;FFR&quot; and &quot;FFR*&quot; would not match as the line starts with a +(orW); &quot;*FFR&quot; would not match because the line does not end in FFR, but &quot;*FFR*&quot; matches because there is an FFR in the middle.

Is there a standard pattern to the data? Meaning will the FFR (or other data code) appear in the same place in each line?

- TK


 
No true patterns...Depending on how many nutritional improvements the manufacture claims will alter the position in the item description...if it's just fat free, FFR will be at the end; however, if it's fat free and low sat, it may be the second to last acronym 🙂

I'll try that spacing appraoch...might get me closer, thanks...yea, it seems to be something with my expressions or whatever...since there are multiple acronyms in a single cell, I figure it started with the first letter and tried to match it with F, then F, then R, etc...so guess I will have to use the first * ...I'm building all my databases now, so I'll try to play around with the spacing in abit...worth a shot 😉 thanks again.
 
theknight571...You, sir, are *DA MAN* 🙂 thanks for the help...can't believe two spaces were holding me back for 2 days ..added the spaces and it works great...I had just grabbed some VBA books and links was going to get started, too 🙂 THANKS!


...and thanks to everyone else who offered some advice/tips/help...you guys are great (for the most part) 🙂
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top