Is this possible to do in an Access Query?

[johnny878]

Here is what I want my query to look like
Product ID Table 1------------------ Product ID Table 2-------------- Product Data Table 2
1111928 ----------------------------1111878---------------------------- xxxxx


Notice the first 4 digits of the Product ID are the same. However the last 3 are different.

What I need to do, is have Access link the Product ID's between the two tables only using the first 4 digits.

Is this possible?

A long round about way that I assume would be possible, but i dont know how to go about it. Would be to create a new column in each table based off the 4 right most digits. Is there a RIGHT() type command in access like there is an excel?

Than once that have the query set up with a link from the two newly created columns and looking like this



Product ID Table 1--------------- Product ID Right most 4 Table 2 ---------------Product Data Table 2
1111928 -----------------------1111------------------------------------------------- xxxx


But once again, that seems like a sloppy way to go about it if thats even possible. Advice on other options is greatly appreciated

[JoeM]

I think you want LEFT and not RIGHT. No need to create a new column in each table, it can all be done via queries.
Set up a query for each table, where you created a calculated column where you take the LEFT 4 characters of that Product ID field, i.e.

MyID: LEFT([Product ID],4)

Then, you can create a new query in which you join these two queries on your newly created calculated field.

[johnny878]

I appreciate the response.

Here is where im stuck.

After I put the MyID Table 1 and MyID Table 2 fields in the query,

How do I set it up where it will take the MyID from Table 1 and link it to the Product Data with the Matching MyID from data 2?

I do not know how to do this in one query. The only way I can think of is to:

1) Add a permanent MyID column in Table 2 as this is a never changing table.
2) Create a new query that generates a My ID Field for Table1. Table 1 has different data every day.
3) create a 2nd query. Simply link the MyID column I made in Table 2 to the newly created MyID field in Table 1's query.

Though, id strongly prefer to keep this to a one query process.

[johnny878]

Here is what I did and I am having an issue that I am struggling to figue out.
1) Table 2 is a permanent never changing table. So, i simply added a "MyID column" that consist of the first 4 digits of the product ID. Did this by doing left 4 in excel and re importing table.
2) In my query, I removed both product ID's from my query. Instead I created a field MyID2: Left(ProductID,4) and had it set to come from Table 1 and SUM. And had MyID from Table 2 in the query rather than Product ID table 2.
The issue I am having, is that the raw numbers are slightly off. Im off like 3 dollars. so something is being missed. thoughts on why this is occuring?

[JoeM]

No, you misunderstand what I am recommending. You will have to set up three queries (or you could make it a one query process if you want to create subqueries using SQL), but the good news is that once set up, it is all dynamic. You won't need to do anything else except open the last query in the future. If you try to store it in table columns, you will need to run preliminary update queries every time you get new data.

All you need to do is this:
Query 1 - Set up the calculated 4 digit ID field for Table1, like I showed you in the previous post, and add all the other fields you want to see.
Query 2 - Do the same this as above for Table 2.
Query 3 - Create a new query linking Query1 and Query2 together on your newly created ID fields, and return whatever fields you want.

The great thing about this is it is all dynamic. As you add new data to your tables in the future, all you will need to do is open Query3 to get your results.

[johnny878]

Table 1 isnt a static table itself.

Its a daily sales report, that I download off the web into excel and then import into access. So creating a static field for the first 4 digits in table 1 really isnt an option. As its a whole new table every day.

Thanks for all the help though.

Is there not a way I can just have access only read the first 4 digits of the product id's in the query without having to create new fields or columns? Like an expression?

[JoeM]

Its a daily sales report, that I download off the web into excel and then import into access. So creating a static field for the first 4 digits in table 1 really isnt an option. As its a whole new table every day.

Where am I telling you to use a Static field? As matter as fact, I am telling you to do quite the opposite.

Is there not a way I can just have access only read the first 4 digits of the product id's in the query without having to create new fields or columns? Like an expression?

That's exactly what I have been telling you to do in the past few posts!!!
Do you know how to create calculated fields in Access queries? I gave you the formula.

If you do not know how to create calculated expressions in an Access query, take a look at Access's built-in help files, or you can Google it (see: http://office.microsoft.com/en-us/ac...005188023.aspx).

Once you set up these queries the first time, you will never have to change them. You can import new data into your tables, and if you open the last query, it will give you what you want. You do not have to write anything back to the original tables, the queries do all the work on the fly and return what you want.

[johnny878]

Ok. I have figured it all out. Thanks for your help. However, while im here and it seems like you know your stuff.

I want to make a query based of the results off the query i just created.

The query i just created has 4 fields-
"1. Product A 2) Product A fee 3) Product B 4) Product B Fee"

Is there a way in a new query i could combine Product A and Product B columns so they show up as one column. And combined the Fee columns as they show up as one column.
So, that product A codes still match up to product a fees. And same with product B. Basically, Id like this query to simply have two columns "product" and "fee"

[JoeM]

I am a little confused. If you do not want the A products and B products on the same record line, why are we linking them together in a query in the first place? What is the purpose of that? Or is this totally unrelated to the original question (where you are not doing any join on the 4 digit ID here)?

I am trying to get a good understanding of exactly what it is you are trying to do to make sure I give you the best possible advice. Maybe if you can post a small of your data and expected results, it will become clearer as to what you are trying to accomplish.

[johnny878]

This is totally unrelated to my original question. I have that one working flawlessly.

Here is a sample of what a line in my original query would look like

Total FEE-------Product A----------Product A Fee--------Product B---------- Product B Fee
$10--------------523---------------7$---------------------119----------------$3
$23--------------287---------------$19---------------------523---------------$4

I want my next query to look like,

Product------------------------Fee
523----------------------------7
119----------------------------3
287----------------------------19
523-----------------------------4


IS that Possible? I dont want a simple concantenate because I do not want the values added together.

Thanks youve been a big help

p.s. These product codes are different than te original 4 digit number in my original query.

[JoeM]

I assume then that these two Products (A and B) are in there own tables, that have similar structures.

If so, then simply create two separate queries from the original tables, that return the Product and Fee values, and then join those twi queries together in a Union Query (see here: http://office.microsoft.com/en-us/ac...010206109.aspx).

You will need to change your query to SQL View to do this. You could actually do it all in a single query with SQL, something like this:

SELECT PRODUCT, FEE
FROM TableA
UNION
SELECT PRODUCT, FEE
FROM TableB;

Note: Depending on the design of your database and whether or not you have the ability to change it, if these two tables have the same structure, it might make more sense to just have a single table to store the data from all your products, and just have a Product field in it that denotes which product. In that situation, this would be a very easy task, as it would just be a simple Select query from a single table.