gary said:
There are NO spaces in front of the data.
Yes I understand that there's no spaces in front of the data.
Your other post suggest that after you import the data, then you do have a
bunch of space in front.
However, I deleted the first 20 or so records from the allpartnbrs.txt
file.
Ah, ok, great, it sounds like you solved the blank character problem.
Do your instructions apply to Access 2007? (They don't seem to be
accurate).
For example, in Access 2007:
There is no "query builder" but there is a "Query Design?"
Sure you'll have to make a bit of assumptions here. query creator, query
builder, query designer, query system,
Sure they're all much the same terminology in this context.
Does "drop in your allpart number table into the query builder" mean
the say thing as "put "left([partNum],9) in the first column, select
partNumPart?
Are you talking about the query that matches the two tables or the query
that reads our field which is nine characters long, and creates a new
column?
As I mentioned we have two possible ways to solve this problem:
We can chop off the additional characters after 9 during the import process.
I think due to your skill level this would actually be a bit easier for you
than using the left("your text",9) function and building a query.
Either way is quite fine. Since it seems your import went OK then it is a
bit late to use the import feature to strip the extra characters. If what
follows is a little bit too difficult than perhaps you should try
reimporting the data again, and during the import have access strip out the
extra characters.
As the stands now we'll go with the second approach. The second suggestion
is thus to create a new field in table allpartnumbers that is only nine
characters long.
After we create this new field, we then run a query to update it with the
first nine characters from the larger field
(in effect will going to move the first nine characters from our origial one
column to another new column)
After we've accomplished this transfer process in this one table, we then
can we'll build and other new query in which we match both tables.
So, first let's open up the allpartnumbers table in design mode. (again I
don't know what your table names are so you have to do a bit of assuming
here).
When the database is open on the left side you should have a navigation
pain. from that left navigation pain you want to right click on your table
and select design.
Now, add a new column to the table. Give this new column name (or is what is
often referred to as a field name) any name you want. something descriptive
like shortpartnum will work just fine here.
For the data type setting use text.
Now close the table, you will be prompted to confirm you changes to the
table design.
this gives us our second column in the table. is this field/column in which
we will move the first nine characters from the first column this table.
thus we now create a new query.
(Click on create tab, and from the ribbin select query design)
At this point the query designer should be asking what table to use for this
query.
Select your table from the list and then click the close button on this
dialog prompt.
Now from the ribbion, You'll see something called query type. You want to
change this query to an update query (use your mouse cursor to hover over
the options - it is usually the second one from the last (Looks like a
little pencil beside an exclamation mark - select this option to change the
query to its update query).
when you choose this option you'll notice how the query grid changes and
adds a new row called
Update to:
So in the first column of the query builder you can use a combo box to
select the NEW field That made above to accept the nine characters of data.
We're going to take the first nine characters from that long data field and
into our newly created column.
It is in the update to row where we will enter expression with the left()
function.
it should look lik:
Update To: left([NameOf1stCollumWithData,9])
you can now run this query (In the ribbon, you'll see a result tab, and a
big ! mark called run. click on this to run).
at this point if this is been done correctly will have a new column with a
nine characters. you can now close this query we're done with it and
actually don't need it anymore.
now at this point are going to create a new query, the process is a similar
to the first time around. however this time we're going to drop in both
tables into the query grid. this is where we use the mouse to drop I join
line between the two fields in the two tables. We can this join line because
we now have a matching column in each table with which to match the data we
have.
asked you'd rather join line you can also pull a couple of fields from
either table into the query builder grid. when you run this query you'll get
matches between both tables.
The above all assumes that you import was correct and you've clearly read
and answered my other questions. The most important detail here is it safe
to assume that all the characters after the nine characters in that one big
column can be ignored? In other words is the data after the 1st name
characters you imported are not of importance?
