If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|