Microsoft Query SQL issue in Excel

[Chewy149]

Hi,

I'm new to the forum, so If this question is misplaced, i apologize.

I'm working within an Excel file using a Microsoft Query and am running into an issue when I'm adding something to it.

Currently the Query works as designed. I'm pulling information from 2 tables within a Microsoft Access Database. I am trying to add a couple of Data Fields from a third Table but when i do, the Query loses rows based on a WHERE clause that is automatically getting added to the SQL code when I add the tables to the query.

After adding the 3rd Table, the SQL WHERE clause is updated to include the following:

User_Values.User_Values = Job.User_Values​

The whole WHERE clause looks like this:

WHERE Delivery.Job = Job.Job AND User_Values.User_Values = Job.User_Values AND ((Job.Order_Date=?))​

I am trying to use a feature never used in our company before where there is a "Custom" Tab contained in a "Job" inside our MRP system. I've found that the Data Fields on the "Custom" Tab are held inside the "User_Values" Table inside the Access Database. Since the fields have never been used previously, the User_Values Table does not contain User_Value IDs. Only when I enter something into a Custom Field will the system assign a User_Values ID.

What I believe is happening is that the WHERE Clause is looking for Jobs that has User_Values that Match a User_Value inside the User_Values Table.....Since the Jobs that don't have anything in the Custom Fields do not have a User_Values assigned, those jobs are being lost in the Query.

Does anyone know of a way to modify this query so that I am able to still access the 2 Custom Fields that I need from User_Values Table and still show Job Rows that may not have a User_Value ID associated to it?

Please Note: I do not have access to modify the original Database in any way... just pull data from it.

 

[Fazza]

From a quick reading of the question, there are three tables, and the issue is that one of the tables lacks data in some fields.

It sounds like the current query is defined to return records that match, and with nulls in one table this query is not suitable. The query design should change. Maybe to an outer join.

One approach is to read MS Query help on table joins. And work out what changes to the SQL are needed to give the desired results dataset.

Or a little bit similar, query just the three tables and set them up in a new, standalone MS Access file so you can play around with joining tables to work out the correct query. Then copy its SQL into MS Query.

Or a little bit similar, have samples of the three data tables & post them to a forum like this. Provide sample input data, the matching output result, and a clear description of the rules/method used to get from the inputs to the output.

For sure you'll have a better audience (to help answer the question) in the MS Access forum.

cheers

 

[Chewy149]

Fazza, thanks for the input. Unfortunately I’m very inexperienced with SQL and Access. I’ve been modifying an existing query to make it work differently, so creating a new one from scratch is beyond my abilities at this time.

Your response tho gave me a good work-around: I can copy the excel worksheet with the query to another worksheet and modify it to work with the new table that’s causing the issues.... then I can do vlookups or something to join the necessary data to the query on the original worksheet.

Thanks!

 

[Fazza]

well done