Query and Primary Key Problems 1

[soahc00]

Hey guys, I have a existing DB which tracks our hardware releases. We have a table that has two columns, releases and primary key. We have a form in which the user inputs the release for the project he/she is working on. I noticed that when they select the release and the information is stored the primary key is saved instead of the text.

I'm running a query that checks for the progress on a particular release. It is in SQL, how would I go about making the query refer to the table so instead of when it runs the query it will display "Release 1" instead of "1"??


THANKS

 

[PHV]

In your query join the releases table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[soahc00]

I joined the releases column in the query to the releases column in the release table, no success, any other ideas?

 

[PHV]

Can you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[soahc00]

SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs1] AS Hours, [tracking].[task1seg] AS Segment, [tracking].[release1] AS Release
FROM tracking
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs1 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs2] AS Hours, [tracking].[task2seg] AS Segment, [tracking].[release1] AS Release
FROM tracking
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs2 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs3] AS Hours, [tracking].[task3seg] AS Segment, [tracking].[release1] AS Release
FROM tracking
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs3 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs4] AS Hours, [tracking].[task4seg] AS Segment, [tracking].[release1] AS Release
FROM tracking
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs4 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs5] AS Hours, [tracking].[task5seg] AS Segment, [tracking].[release1] AS Release
FROM tracking
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs5 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs6] AS Hours, [tracking].[task6seg] AS Segment, [tracking].[release1] AS Release
FROM tracking
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs6 <>0 )
ORDER BY Day;

 

[PHV]

I see no JOIN here ...
The basic idea:
SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs1] AS Hours, [tracking].[task1seg] AS Segment, [Release table].[release name] AS Release
FROM tracking INNER JOIN [Release table] ON tracking.release1=[Release table].[Primary key]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[soahc00]

HEY PHV, thanks for your help so far.....I appreciate it very much. I've used the following code and I get "Type Mismatch In Expression", do you have any shots as to why?

Thanks


SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs1] AS Hours, [tracking].[task1seg] AS Segment, [tracking].[release1] AS Release
FROM tracking INNER JOIN [releases] ON [tracking].[release1]=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs1 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs2] AS Hours, [tracking].[task2seg] AS Segment, [tracking].[release2] AS Release
FROM tracking INNER JOIN [releases] ON [tracking].[release1]=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs2 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs3] AS Hours, [tracking].[task3seg] AS Segment, [tracking].[release3] AS Release
FROM tracking INNER JOIN [releases] ON [tracking].[release1]=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs3 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs4] AS Hours, [tracking].[task4seg] AS Segment, [tracking].[release4] AS Release
FROM tracking INNER JOIN [releases] ON [tracking].[release1]=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs4 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs5] AS Hours, [tracking].[task5seg] AS Segment, [tracking].[release5] AS Release
FROM tracking INNER JOIN [releases] ON [tracking].[release1]=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs5 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs6] AS Hours, [tracking].[task6seg] AS Segment, [tracking].[release6] AS Release
FROM tracking INNER JOIN [releases] ON [tracking].[release1]=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs6 <>0 )
ORDER BY Day;

 

[PHV]

The release1 field of the tracking table, if used as a Foreign Key referencing the Releases table, must be of the same data type as the Primary Key of the Releases table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[soahc00]

I'm not sure I understand PHV, the release1 field is a string, and the ID/Primary Key of the Releases Table is a number. So what You're saying is that this can't be done?


How then would I tell the query this then....

Query Runs --> Pulls out needed info including releases --> Has # values in results of query --> Recognizes numbers should be joined with the primary key of the releases table --> Joins -> Displays release string instead of number?

 

[PHV]

I think you have a design issue.
Anyway you may try this workaround:
FROM tracking INNER JOIN Releases ON Int(tracking.release1)=Releases.ID
You may take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[soahc00]

That cleared up the error, but there are still numbers instead of text......any ideas?


Also, sorry to be such a pain in the ass....


SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs1] AS Hours, [tracking].[task1seg] AS Segment, [tracking].[release1] AS Release
FROM tracking INNER JOIN [releases] ON Int(tracking.release1)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs1 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs2] AS Hours, [tracking].[task2seg] AS Segment, [tracking].[release2] AS Release
FROM tracking INNER JOIN [releases] ON Int(tracking.release2)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs2 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs3] AS Hours, [tracking].[task3seg] AS Segment, [tracking].[release3] AS Release
FROM tracking INNER JOIN [releases] ON Int(tracking.release)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs3 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs4] AS Hours, [tracking].[task4seg] AS Segment, [tracking].[release4] AS Release
FROM tracking INNER JOIN [releases] ON Int(tracking.release4)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs4 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs5] AS Hours, [tracking].[task5seg] AS Segment, [tracking].[release5] AS Release
FROM tracking INNER JOIN [releases] ON Int(tracking.release5)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs5 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs6] AS Hours, [tracking].[task6seg] AS Segment, [tracking].[release6] AS Release
FROM tracking INNER JOIN [releases] ON Int(tracking.release6)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs6 <>0 )
ORDER BY Day;

 

[PHV]

Seems you haven't read carefully my 3rd post.

[Release table].[release name] AS Release

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[soahc00]

Thank YOU!!!