LEFT or RIGHT functions

Yes.





SELECT [E-commerce].*, [IDS Order Detail (E-com)].*
FROM [E-commerce] LEFT JOIN [IDS Order Detail (E-com)] ON
[E-commerce].REFNO = [IDS Order Detail (E-com)].[ORDER NUMBER];

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

That worked. Now expanding the process logically-- can I include additional
fields? This query returned 12000 records. Am I able to include payment
data from each of the tables so that I am, in effect, using the query to ask
this question: "Please show me all order numbers which appear in the
E-commerce table as well as the IDS Order Detail table, and show the cost to
ship for each item (represented by fields [PAY] and [ORDER TTL FREIGHT AMT])."

In effect, the E-commerce table contains order details, including what my
company paid to ship each order. The IDS... table contains additional
details, including what the customer was charged. We would like to compare
what we paid vs. what we charged.

Does that make sense?

"KARL DEWEY" wrote:

Go with this --
SELECT [E-commerce].*
FROM [E-commerce] INNER JOIN [IDS Order Detail (E-com)] ON
[E-commerce].REFNO = [IDS Order Detail (E-com)].[ORDER NUMBER];

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

This is the SQL after doing exactly as is seen in this post:

"KARL DEWEY" wrote:

In query design view put both tables in the space above the grid.
Click on the OrderNumber field in TableA and drag to TableB OrderNumber field.

SELECT
FROM [E-commerce] INNER JOIN [IDS Order Detail (E-com)] ON
[E-commerce].REFNO = [IDS Order Detail (E-com)].[ORDER NUMBER];


"KARL DEWEY" wrote:

That returned an empty field.
What does that mean?

I clicked on "REFNO" in one table and dragged to "Order Number" in my other table, above the grid, creating a connector line between the two.
Use the table that has the field without the prefix and drag to the QUERY
that removed the prefix from the field. You see they onlt join if matched.
The idea of the query was to remove the prefix so they would match.

I then selected a blank field in my "A" column,
What is your "A" column?

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

That returned an empty field. I clicked on "REFNO" in one table and dragged
to "Order Number" in my other table, above the grid, creating a connector
line between the two. I then selected a blank field in my "A" column, as I
received an error message when trying to run the query without a destination
field.

Both sets are formatted as text in design view, so I don't think it is an
issue of unmatched formats.

Any ideas?

"KARL DEWEY" wrote:

In query design view put both tables in the space above the grid.
Click on the OrderNumber field in TableA and drag to TableB OrderNumber field.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

Karl, thank you for the help. It worked perfectly!

Any recommendations for comparing Table A to Table B using the common data
of order numbers? I have external shipment data in one table and internal
data in another and want to match the records for an order number in each
table so that I can compare what we paid to ship something to what we charged
our customer for shipping.

Now that I have the order numbers cleaned up in my "A" table, they match the
order numbers in my "B" table. I'm sure Access has some sort of tool to look
at Table A and pick out all the lines where an order number matches one found
in Table B. Am I right?

"KARL DEWEY" wrote:

Yes. Backup the database!
Create a select query with field REFNO twice. Edit the second to look like
this ---
Expr1: Right(Trim([REFNO]),7)
Run query and review results.
If it look ok then change query to an Update query.
Copy all after Expr1: and paste in the Update To row of the
design view grid under field REFNO.
Run the query.
Change query back to select, run, review results.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

Ok so as I learn more I figure out more. It looks like my original REFNO
Field includes extra spaces at the end, which is why the Query is returning
an empty dataset (As a sanity check, I ran a LEFT function in a query and
that returned results). Here is what I think I need to do ultimately:

1. Trim the extra spaces from the REFNO column.
2. Run the Right function to eliminate the prefix that exists on some of my
order numbers.
3. I would like to have my original table (about 25 fields) update with the
"new" order numbers, which at this point would no longer have prefixes.
4. Then I can use this table to compare to another table which contains the
data I need to compare to these orders (this second table only contains
orders WITHOUT prefixes, which is the point of this exercise to begin with.)

Can I accomplish 1, 2, and 3 in an Update Query with the proper SQL
functions built in? If so, does that Update Query need to include all fields
or just the REFNO field? And how do I run both the Trim function and the
Right function together?

"Wildcats" wrote:

Arvin:

I opened a new query in Design View. I selected my table "E-commerce." In
the Field, I entered:

=Right(REFNO,7)

Then I looked at the SQL view and it appeared just as you note it would in
your post. However, the query returns an empty dataset. Any ideas?

"Arvin Meyer [MVP]" wrote:

Order_Num: is what is known as an alias. If you just type:

Right(REFNO,7)

into a column, it will add the default Expr1: as the alias. Look at the SQL
view and you'll see something like:

Select Right(REFNO,7) As Oreder_Num From YourTableName
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Wildcats" wrote in message
...
It kills me to have ask ask questions like this, but should I format like
this:

Order_Num: Right(REFNO,7)

Where "REFNO" is the field in my table which contains the order numbers?
Or
do I need to replace Order_Num with REFNO?

And do I plug this into a query or elsewhere?

As someone who is smart and can usually pick up new computer apps quickly,
I
have been very frustrated by trying to learn Access on the fly, so I
appreciate the help and the simple terms.

"KARL DEWEY" wrote:

I assume you want the right 7 digits. Just use the Right function like
this --
Order_Num: Right([YourField],7)

If there are 7 digits or 11 it will work the same.

--
KARL DEWEY
Build a little - Test a little


"Wildcats" wrote:

I am brand-new to Access. I am using it at work for the first time and
am
learning on the go. In the long term, I have the opportunity for
formal
training, but in the short term I am working on a project with Access
that
needs to be completed ASAP.

Here is my question: I have used the LEFT or RIGHT function in Excel
and
know you can do a similar operation in Access to reduce data. In my
current
project, I have a column with order numbers. Some have a three digit
prefix
(like PRE-) with a dash, followed by a seven digit number. I need to
make
these the same format as entries that do not have the prefix. This is
for
the purpose of comparing these orders to entries in another table that
does
not support the prefix.

I am hoping someone can provide some newbie-friendly advice on how to
accomplish a task I am sure is very simple once someone turns the light
bulb
on for me...

Thanks!