help with my query...again

[kevmeister123]

Hi again,

I had a query problem a couple of weeks ago which Remou very kindly helped me fix. I have since had to re-order a few things in my database and the query stopped working, but i don't know how to get it working again. Please Help.

My sheep management database consists of several tables: sheep, breed, category, status.

The latter 3 consist of only a index auto number field and one other text field.

The sheep table consists of id, breed (select from breed table), category (select category table), status (from status table), and a number of other fields which are directly typed in.

I want a query to list all sheep with for example "Sold" in the status column. However, the best I can get it to do is it displays 1 line only - If sold is index 3 (in the status table) then it will display "Sold" as well as the contents for index 3 from the sheep table. I know it's probably something to do with my relationships but i've been playing around for ages trying to get it working.

Any ideas?

Many thanks.

Kev

 

[Remou]

When you have a problem with a query, it is nearly always a good idea to post the SQL.

 

[kevmeister123]

Okele Dokelee
Here is what you gave me last time:

SELECT tblSheep.ID, tblStatus.Status
FROM tblSheep INNER JOIN tblStatus ON tblSheep.StatusID = tblStatus.StatusID
WHERE tblStatus.Status="Sold"

Here is what I have at the moment (for some reason when i delete the brackets it puts loads of square brackets around all the other table references:

SELECT Sheep.SheepID, Sheep.Breed, Sheep.Category, Status.Status
FROM Sheep INNER JOIN Status ON Status.StatusID = Sheep.SheepID
WHERE Status.Status="Sold";

If I go by spot the difference, I can see the diff in the FROM bit at the end, but when I put them how you wrote them I get a message that the fields have been deleted, So I added the fields and then get a "mismatch" error.

Thanks again

 

[Remou]

You are joining StatusID to SheepID. This will not work. You need to join StatusID in the Sheep table to StatusID in the Status table, assuming this set-up:

[tt]Status Sheep
StatusID Status SheepID Status
1 Sold 10 1
2 Married 15 1
3 Emigrated 20 2[/tt]

Delete your current join and create the new one by dragging one status field to the other. (PS there is a dedicated query forum701)

 

[kevmeister123]

Ooops, sorry, i didn't realise there was a thread specifically for queries.

I think I understand what saying, but there is no StatusID in the Sheep table, only a Status field.

Sooooo I should add a field called StatusID to the Sheep table, then simply link them?

i'll give it a try

many thanks

kev

 

[kevmeister123]

I tried and I failed :O(

I created the new field in the Sheep table (called StatusID) linked it to the StatusID of the Status Table.

Then re-created the SQL bit like the original.

I don't get any error messages, but I get the query with the headings but no cells or data.

 

[Remou]

What does the status in the sheep table look like?
What does the status table look like?
What is the SQL for your new query?

 

[kevmeister123]

Sheep table (there are some other fields, but i didn't have enough space to put them in)

SheepID | Breed | Category | Cost | SellPrice | SoldTo | Status | StatusID
1 | Texal | Ram | £30 | | | Current Stock | |
2 | Suffolk | Ewe | £40 | £50 | John | Sold | |
3 | N Cheviot | Ram | £40 | £60 | John | Sold | |
4 | B Cheviot | Ewe | £30 | | | Died | |


Status Table

StatusID | Status
1 | Current Stock
2 | Sold
3 | Died

Query SQL

SELECT Sheep.SheepID, Sheep.Breed, Sheep.Category, Sheep.Status
FROM Sheep INNER JOIN Status ON Sheep.StatusID = Status.StatusID
WHERE (((Status.Status)="Sold"));


I hope all this makes sense when i post it.

 

[randy700]

The JOIN works like a WHERE clause. In your case, you want output where Sheep.StatusID = Status.StatusID. According to the data you posted, there is nothing that meets this criteria, which is why you get an empty recordset. You need to put some data into the Sheep.StatusID field. You should also remove the Sheep.Status field because it is duplicated data. Obtain it from the Status table.

Or, change your SQL to read...
INNER JOIN Status ON Sheep.Status = Status.Status
...but I don't recommend this method.


Randy

 

[Remou]

If this is the real data, you have not got anything in the sheep table StatusID field. All sold sheep need to have a StatusID of 2. The general idea of these joins is that they match all 'whatever' in tblA to all 'whatever' in tblB, so if you do not get anything returned, you need to look at your data.

 

[Ration]

Since this query is relatively simple to accomplish, I would just build it using "Design View".

Add your needed columns to the query such as "Breed" and "Cost". You must either add the "Status" or "StatusID" field because you are querying information based on the contents of either of these fields.

Once you have all of your fields in place, in the criteria box beneath either "Status" or "StatusID", type "2" for StausID in the corresponding Criteria Box or type "Sold" in the Criteria Box under "Status".

Run the query. It should populate an entry for each sheep that either has "Sold" in the Status Column or "2" in the StausID column.

Hope this helps!

 

[kevmeister123]

Hooraay.

It's all now working, I removed the status column from the sheep table and just left the StatusID in the sheep table.

The reason I didn't do that before is I wanted to display eg "Sold" and not "2". But all i did was set the statusID to display the data field, column widths - 0cm,3cm which does the same job. Then the query started working on it's own.

Many Thanks for your help everyone :O)

ps. if i have anymore query questions i'l post them in the query forum ;O)