query to display data in two tables

[taimysho0]

hello ive attached my database, how do i make a query to show all the columns in 'KEY' and match it up with all columns in 'Testdata' and look at the ticket count from test data? if there is no category from testdata that matches 'key' then ticket count value will be null or zero?



this is what i have so far but doesnt show values for ticket count (all null):


SELECT KEY.[Product L1], KEY.[Product L2], KEY.Category, testdata.[Ticket Count]
FROM [KEY] LEFT JOIN testdata ON (KEY.Category = testdata.Category) AND (KEY.[Product L2] = testdata.[Product L2]) AND (KEY.[Product L1] = testdata.[Product L1]);




test.accdb

[JoeM]

Use the NZ function to tell it what to return in the case of no match (zero). NZ, by default, returns a text value, but adding zero to it will coerce it into a numeric entry, i.e.

SELECT KEY.[Product L1], KEY.[Product L2], KEY.Category, NZ([testdata]![Ticket Count],0)+0 AS Ticket_Count
FROM [KEY] LEFT JOIN testdata ON (KEY.Category = testdata.Category) AND (KEY.[Product L2] = testdata.[Product L2]) AND (KEY.[Product L1] = testdata.[Product L1]);

[taimysho0]

hello i tried your code but its returning zeros for all of my results in the query. (attached image)

[June7]

That indicates [Test Count] has Null in each record.

Are you including Key table so all Keys are shown even if there is no ticket count?

The query looks correct. If you want to provide db for analysis, will look at.

[JoeM]

hello i tried your code but its returning zeros for all of my results in the query. (attached image)

The query is actually working correctly. The problem is that you have a data issue.
Take a look closely at your Key table, specifically the "Product L2" and "Category" fields. Note that you have a blank space before every entry in these fields.
Since you are including these fields in your Joins, none of these records match the records in the other table. You need to get rid of those extra spaces, and then things should work correctly.

[taimysho0]

The query is actually working correctly. The problem is that you have a data issue.
Take a look closely at your Key table, specifically the "Product L2" and "Category" fields. Note that you have a blank space before every entry in these fields.
Since you are including these fields in your Joins, none of these records match the records in the other table. You need to get rid of those extra spaces, and then things should work correctly.

thanks for the help! im still having issues even after i took out the spaces. Since my original data is in Excel, can you help me with creating a macros in excel where it does this? ive attach my sample doc. Basically i want sheet 1 to do a v lookup in sheet 2 and look at rows 2 through 29 and see what is missing in sheet 1. The rows that are missing should be appended to Sheet 1 and for columns "ticket count", the value should be a zero, is this possible to do with macros?


thanks for all your helpTESTDATA_current.zip

[June7]

You want help for code in Excel to create records? I expect that would be far more complicated then doing the same with Access tables.

[taimysho0]

ah i see, wasnt sure if that was easier. in that case ive provided my test database below if you want to take a look why the query above still is not printing the values, thanks for your timetest.accdb

[June7]

No data show in Ticket Count because no records in testdata match the records in Key table. The query is set to "Include all records from Key ..." and has compound join on 3 fields. As an example, the Key combination of {Accounts/Other/Close Duplicate Account} has no matching combination in testdata, therefore no Ticket Count.

If you need to create records in Key for the non-matching values in testdata, that means using a Find Unmatched query and using the results in an INSERT sql action (or do a manual copy/paste).

[taimysho0]

Thanks! i got it to work, i concatenated product l1, product l2, and category into one column on Key and testdata tables. the only problem is the raw data file comes with the columns split into 4 columns (product l1, product l2,category, and ticket count columns). in excel, i run this formula to combine the 3 columns into 1 by using =CONCATENATE(A9,">",B9,">",C9,) and then i import into access.

if i were to import the raw file into access, can i run a query that does the same thing as this formula? basically a query that concats these columns together?

[June7]

The concatenation should make no difference in the query results so I don't understand what you did that you say worked.

[JoeM]

Thanks! i got it to work, i concatenated product l1, product l2, and category into one column on Key and testdata tables. the only problem is the raw data file comes with the columns split into 4 columns (product l1, product l2,category, and ticket count columns). in excel, i run this formula to combine the 3 columns into 1 by using =CONCATENATE(A9,">",B9,">",C9,) and then i import into access.

if i were to import the raw file into access, can i run a query that does the same thing as this formula? basically a query that concats these columns together?

There is no need to concatenate this fields together to join them. You can join on multiple fields in Access just as well as you can join on one field.
As I said before, the issue is your data. Your values are not equal. Extra spaces matter!
"Other" is not the same as " Other"
and "Request to Merge Accounts" is not the same as " Request to Merge Accounts".

You can use the TRIM function in an Update Query to fix the data in your Key table, i.e.

UPDATE [KEY] 
SET [KEY].[Product L2] = Trim([KEY]![Product L2]), 
[KEY].Category = Trim([KEY]![Category]);

Do this, and then your query will work like it is supposed to.

Also, to get zeroes to return for the records without matches, in your Query1, replace the "Ticket Count" field you are returning with this calculated field:

TicketCount:NZ([testdata]![Ticket Count],0)+0

The NZ function basically tells it what to return if there is no match found.

[taimysho0]

^thanks, so yes youre right there was a data issue, i had those extra spaces you mentioned but once i concat the columns i realized they two tables were not matching due to the spaces. regardless, id like to keep it all one field because the end result of my application is to graph each of the rows in the resulting query so it would look nicer if all the columns were combined.


from the raw data in excel, i need to format it a certain way before i graph. im no excel wizard but ive attached my test excel file and i need to do a macros in excel that does steps 1-9 in the file. if you guys can help me with this, itll make my day and i can mark this thread as resolved. thanks again!TESTDATA_current (2).zip

[June7]

Correction to my earlier comments:The posted data still had the leading space in each value in Key table so that is main reason some of the records were still not showing Ticket Count. The other part of issue is data in each table still not matching.

It is best to store the values in separate fields then use queries to concatenate for other purposes.

Graphing can be done in Access.

[taimysho0]

if any of the steps in my excel attachment^ can be done in access please let me know and i can just create the query. basically im creating an app in access where it asks user to select a file, once the file is selected, click upload button. on click event, a whole bunch of queries run in the backend and formats the raw excel file into the end result of 3 columns (category, date, and ticket count). if i can format the file in excel first then import to access, thats fine also