query screw ups

[Alice]

Hello guys,

I've got a query running on the data from one table. When I run the query, I've put a thing for the user to select a specific trailer number to look at.

However, it seems not to pick up the values for only that number, but all the others too, not in the sense that it lists them all, but attributes data belonging to other trailer numbers to the one I've selected.

This really seems strange, but I'd guess it's a saving error, no? I've got it so that each new entry is given a unique id number, which is the critical key.

Pointers would be great if you can,

alice

 

[Corticus]

Hey,
Sounds like a pretty simple select-query. Could you post how the data is organized in the source table, and also what sets the criteria for the query, and while you're at it , how you want your data organized in the query.

-Corticus

 

[Alice]

Ok, new to this, as you can teel from the number of posts i've made so bear with me while i give you the info i think you're asking for....here goes...

The table gives 5 categories of information...
an id number (auto + primary key)
trailer number
date
responsibility
defect description, and
next service date
.....ok, six bits of information, but then who needs to be able to count when you've got a spreadsheet?

The query was made using the wizard, and so i'm not really sure how it's set up. I want it to show all the info stored for each record, but only for the trailer number i've selected, which i've done by setting a critera in the query design, to generate a question when you open the query [select trailer number].....

phew...ok, hope that this makes some sense...

alice

 

[Russell Hauf]

"Bored Regular" - funny!

How about posting your SQL? When you are in query Design view, go to View-SQL view. Copy the SQL and paste it here.

Thank you!

 

[Alice]

SELECT [Defect details].[Trailer Number], [Defect details].[Defect description], [Defect details].[Date], [Trailer data].[Receiving location], [Defect details].[ID]
FROM [Trailer data] INNER JOIN [Defect details] ON [Trailer data].[ID]=[Defect details].[ID]
WHERE ((([Defect details].[Trailer Number])=[Select a trailer number]));


I really hope this helps...what's SQl, or SlQ?

Alice

 

[Corticus]

Ok,

try this:
SELECT [DefectDetails].[Key], [DefectDetails].[TrailorNumber], [DefectDetails].[Date], [DefectDetails].[Responsibility], [DefectDetails].[DefectDescription], [DefectDetails].[NextServiceDate]
FROM DefectDetails
WHERE ((([DefectDetails].[TrailorNumber])=10));

You'll notice I renaimed your fields with spaces without spaces. You should do this, as well. The test I did works correctly, so hopefully this will take care of it. Oh, and the part that says'WHERE (((DefectDetails.TrailorNumber)=10));' is your criteria, and you will input this number.

edit: Not sure about the INNER JOIN, that could be causing problems...hard to tell

editII: SQL is Standard Query Language. Its an attempt at a universal language to allow retrieval of data from tables made in a variety of database applications, including MySQL, Oracle, Access etc. Any query in Access can be expressed(and is) in SQL. Some queries, such as UNION queries can only be written in SQL.

HTH,
Corticus
This message was edited by Corticus on 2002-12-18 11:32

 

[Russell Hauf]

Actually, your query looks ok. You say that you are getting all records no matter what you type in? Can you tell me what the data type of field Trailer Number is? If you don't know, open the Table (Defect Details) in Design view, and you will see the Data Type next to the field name. Also, could you post some sample data from both tables? Sorry to go through so much trouble, but I think we can work it out.

-Russell

 

[Alice]

Hi there -
no, i'm not getting all records, but data about other trailers, attributed to the number i put in, eg if I enter trailer 1, and trailer 2 had a broken wheel, the query will say trailer 1, broken wheel, if you get me....I think it's more to do with saving of data than of the query itself....

 

[Russell Hauf]

Nope. Your data is saved before you run a query. Your query can not bring back "unsaved data." Can you give an example of your data and an example of what comes back. Also, can you tell me the field TYPE of the field you are trying to filter on (Trailer Number, I think it is)?

Thx,

rh