Problems building a simple query

[BlondeBimbo]

I am struggling to create a query in access.

I have two tables say “Things” which contains the fields, Moved, fromCustomer and ToCustomer

with a couple of entries such as

bananas (moved) Customer1 (fromCustomer) Customer2 (ToCustomer)

apples (moved) Customer3 (fromCustomer) Customer1 (ToCustomer)



And a “ Customers” table which has fields Name and Location

With entries

Customer1 (Name) London (location)

Customer2 (Name) Manchester (location)

Customer3 (Name) Liverpool (location)

(It’s a test DB as the real tables are much more involved of course)



I am trying to build a query using query builder and design view to basically output three fields:

The item moved (from the moved field);

The Location (obtained from the customer table) of the customer identified in the fromCustomer;

And the location (obtained from the customer table) of the customer identified in the ToCustomer

as so:

Bananas (moved) London Manchester

Apples (moved) Liverpool London



I appear to be able to join the table based on one of the “customers” but can’t get the second to work and visa versa I can only get the results:

Bananas London London

Bananas Manchester Manchester

Could anyone help – it seams such an obvious requirement yet I can’t find anything that works when searching

 

[arnelgp]

SELECT things.Moved, T1.Location As [From], T2.Location As [To]
FROM (things LEFT JOIN Customers AS T1 ON things.FromCustomer = T1.Name)
LEFT JOIN Customers AS T2 ON things.ToCustomer = T2.Name;

++++++++++
you need to "drag" two Customers table in your query.
join FromCustomer of things table to Name field of the
first Customer table.
Do the same on the ToCustomer to the 2nd Customer table.

 

[BlondeBimbo]

Thanks, how would I enter that via the query builder, can you use overwrite the SQL?

I did try writing in SQL but it reverted to the SQL as generated by the query builder/design view

 

[BlondeBimbo]

Ok so I basically deleted the query and used SQL directly, Access accepts this but it doesn't work

I just get cmd boxes requesting Things.FromCustomer, T1.Name, Things.ToCustomer, and T2.Name;

 

[Gasman]

You made up the name "Things" ?

 

[BlondeBimbo]

?

well things isn't the name of the real table, just the example above I am using it for test,

 

[Gasman]

?

well things isn't the name of the real table, just the example above I am using it for test,

And as arnel could not possibly know the name of the table, he used what you were using?
So alter to suit.?

Next time give the correct names if you do not understand anything of SQL. Arnel expected you to change the names to the correct table names.?
Same would apply to any fields.

 

[BlondeBimbo]

And as arnel could not possibly know the name of the table, he used what you were using?
So alter to suit.?

Next time give the correct names if you do not understand anything of SQL. Arnel expected you to change the names to the correct table names.?
Same would apply to any fields.

I did!

This is the actual SQL I used using the actual names In the example DB I built just for this test

SELECT Movements.Moved, T1.Location As [From], T2.Location As [To]
FROM ( Movements LEFT JOIN Customers AS T1 ON Movements.FromCustomer = T1.NameC)
LEFT JOIN Customers AS T2 ON Movements.ToCustomer = T2.NameC;

I did use Movements rather than Things, and NameC rather than Name, but changed the code to align.

As said, the result is dialogue boxes requesting Things.FromCustomer, T1.Name, Things.ToCustomer, and T2.Name

based on the text used in this thread

the actual I get is obviously Movements.FromCustomer, T1.NameC Movements.ToCustomer, and T2.NameC

The actual requirement is of course nothing like this, far more involved and the fields would not make sense, so I generated the example above, then found I actually needed to use different fields such as NameC rather than Name which is reserved.

It still doesn't work of course, not sure why having a go about the choice of table names overrides the actual question about the problem.

 

[BlondeBimbo]

here is the actual DB and query

 

[Gasman]

If Access is asking for fields like Things.Customer, it is because it cannot find that field.? That indicates that the name is being used somewhere?
The same would apply if the field was called Customers in the Movement table and you used Customer ?

So the names are very important. Access is not going to see a typing error and know what it should be, whilst a human would (well sometimes)

I'll leave you to arnel's capable hands.

 

[plog]

The actual requirement is of course nothing like this, far more involved and the fields would not make sense

I've retyped this 3 times trying to make it more polite, so please understand this isn't meant to shame or demean you: Your "simple" example is so simple that if you don't know how to accomplish it without help, I fear you will not be able to apply the solution we give you to the more complex actual system.

What you initially asked for is something you'd learn within the first 10 minutes of an online SQL tutorial (https://www.w3schools.com/sql/). It truly is a simple JOIN. I think that you might get your head around this simple issue with our help but it won't actually help you with your actual issue.

I suggest you read through that tutorial, then if you still need help with your actual issue, you post it.

 

[sgrgeta]

SELECT Movements.*, Customers.Location, Customers_1.Location
FROM (Customers RIGHT JOIN Movements ON Customers.ID = Movements.FromCustomer) LEFT JOIN Customers AS Customers_1 ON Movements.ToCustomer = Customers_1.ID;

 

[Minty]

I have renamed your ID fields, removed the table lookups and made your query for you.
I would suggest you have a read about the evils of lookup fields. http://access.mvps.org/Access/lookupfields.htm

Have a close look at how the query ends up.

SELECT Movements.ItemMoved, Customers.Location AS Moved_From, Customers_1.Location AS Moved_To
FROM Customers AS Customers_1
INNER JOIN
(Customers INNER JOIN Movements ON Customers.Customer_ID = Movements.FromCustomer)
ON Customers_1.Customer_ID = Movements.ToCustomer;

 

[Pat Hartman]

To build this query using QBE
1. Add the movement table
2. Add the customer table
3. Add the customer table again. Access will suffix it with "_1"
4. Close the selection
5. draw a join line from the movement table From field to one customer table
6, Draw a join line from the movement table To field to the second customer table
7. Select the columns from the movement table
8. Select the columns from each customer table BUT give the fields alias names as you select them so that the address fields will be prefixed by From or To giving them unique names.

 

[arnelgp]

From/ToCustomer fields of Movements table have Lookup (ID field of Customers):

SELECT Movements.Moved, T1.Location As [From], T2.Location As [To]
FROM (Movements LEFT JOIN Customers AS T1 ON Movements.FromCustomer = T1.ID)
LEFT JOIN Customers AS T2 ON Movements.ToCustomer = T2.ID;

 

[BlondeBimbo]

To build this query using QBE
1. Add the movement table
2. Add the customer table
3. Add the customer table again. Access will suffix it with "_1"
4. Close the selection
5. draw a join line from the movement table From field to one customer table
6, Draw a join line from the movement table To field to the second customer table
7. Select the columns from the movement table
8. Select the columns from each customer table BUT give the fields alias names as you select them so that the address fields will be prefixed by From or To giving them unique names.

Thank you, I was hoping you could build it using the Query builder.

As it happens, because it wasn't working last week and I needed to get the report/data out to excel I wrote the thing in VBA - added a new table with the data I needed then added a widget. Pressing the widget run the VBA command to extract all the data from the database convert it into the format I needed into this new table, run a simple query to extract the data and output to an excel file, then cleared this new table.

Took about 1/2 - 3/4 of an hour - I had spent way more time trying to get the query working

 

[Pat Hartman]

At least now you know how to do it in 2 minutes and without database bloat.

 

[mike60smart]

Thank you, I was hoping you could build it using the Query builder.

As it happens, because it wasn't working last week and I needed to get the report/data out to excel I wrote the thing in VBA - added a new table with the data I needed then added a widget. Pressing the widget run the VBA command to extract all the data from the database convert it into the format I needed into this new table, run a simple query to extract the data and output to an excel file, then cleared this new table.

Took about 1/2 - 3/4 of an hour - I had spent way more time trying to get the query working

Hi
Point to note about your Movements table.

You should not be using Lookup Fields in tables.

Google "The Evils of Lookup Fields in Access Tables"

 

[BlondeBimbo]

The actual DB doesn't use lookup fields, this query has around 30 individual pieces of data around 24 of which are all from 5 sub-tables, the rest from the primary table

But as I couldn't build one with two look ups, I produced the simplest DB example to illustrate the problem, as once I could build a simple query for two lookups it would be easy to do it for the proper database.

Obviously I cannot actually upload the proper version both from a security and complexity point of view, understanding the relationships of the real DB would take people time, when a simple EXAMPLE would serve just as well.

 

[The_Doc_Man]

Also, just as a side observation: Using a field named FROM is going to vex you, long term, because FROM is a reserved word for SQL, which means you can NEVER drop the square brackets around the field name OR can't do without "qualifying" the field name with its table name.