QUERY QUESTIONS

[hotrod1952 via AccessMonster.com]

Newbie!
I have a query setup which achieves the results I want. It is as follows:

SELECT TOP 15 Asset.AssetID, Asset.AssetDescription, Sum(WOLabor.
WOLaborDuration) AS SumOfWOLaborDuration
FROM (WO INNER JOIN Asset ON WO.AssetNo = Asset.AssetNo) INNER JOIN WOLabor
ON WO.WONo = WOLabor.WONo
WHERE (((WOLabor.WOLaborDate)>=[START DATE dd/mm/yy] And (WOLabor.WOLaborDate)
<DateAdd("d",1,[END DATE dd/mm/yy])))
GROUP BY Asset.AssetID, Asset.AssetDescription
ORDER BY Sum(WOLabor.WOLaborDuration) DESC;

I want to add two fields to the query which I would import as text via a
linked excel spreadsheet . However when I do this My query results change. So
I am doing something wrong. I have set up a second query:
SELECT LaborLink.COMMENT, LaborLink.CODE
FROM LaborLink;
And can run it and it works. I tried copying the SQL from both to a third
query and use a union and union all but get an error because the # of columns
is not the same. How to get around this? Or is another complete idea out
there to simplify the entire situation?

 

[John Vinson]

Newbie!
I have a query setup which achieves the results I want. It is as follows:

SELECT TOP 15 Asset.AssetID, Asset.AssetDescription, Sum(WOLabor.
WOLaborDuration) AS SumOfWOLaborDuration
FROM (WO INNER JOIN Asset ON WO.AssetNo = Asset.AssetNo) INNER JOIN WOLabor
ON WO.WONo = WOLabor.WONo
WHERE (((WOLabor.WOLaborDate)>=[START DATE dd/mm/yy] And (WOLabor.WOLaborDate)
<DateAdd("d",1,[END DATE dd/mm/yy])))
GROUP BY Asset.AssetID, Asset.AssetDescription
ORDER BY Sum(WOLabor.WOLaborDuration) DESC;

I want to add two fields to the query which I would import as text via a
linked excel spreadsheet . However when I do this My query results change. So
I am doing something wrong. I have set up a second query:
SELECT LaborLink.COMMENT, LaborLink.CODE
FROM LaborLink;
And can run it and it works. I tried copying the SQL from both to a third
query and use a union and union all but get an error because the # of columns
is not the same. How to get around this? Or is another complete idea out
there to simplify the entire situation?

Well, a UNION joins two (identically structured) recordsets "end to
end". You want to join the LaborLink fields "side to side".

How does the LaborLink table relate to the WOLabor table? Is there a
CODE field in WOLabor, or some other field?

I think you just need to open the Totals query in design view; add the
LaborLink table to the grid; join it on the appropriate field,
whatever that might be. If there are records which do not have a
LaborLink Comment but you want to see them anyway, select the Join
line and choose the option

SHOW ALL Records IN WOLabor AND MATCHING RECORDS IN LaborLink

In the SQL this will be a LEFT JOIN instead of the default INNER JOIN.

John W. Vinson[MVP]

 

[hotrod1952 via AccessMonster.com]

The two tables are not related. There is the source of the problem. To relate
them I created a column in the excel sheet called assetid and linked that
column to the assetid column from the asset table. I failed to set the Excel
sheet up with a drop down box or something to limit the inputs into the
column to only those in the assetid field of the asset table in access. That
is why the data was wrong because I discovered a typo in the asset field.
What I may have to do is write the first query less the two excel fields and
have those results put into a new table in access. Then set up a form to
enter text into the same table and pull those results out via a report of
some kind and do away with the excel spreadsheet.

Newbie!
I have a query setup which achieves the results I want. It is as follows:

[quoted text clipped - 17 lines]

is not the same. How to get around this? Or is another complete idea out
there to simplify the entire situation?

Well, a UNION joins two (identically structured) recordsets "end to
end". You want to join the LaborLink fields "side to side".

How does the LaborLink table relate to the WOLabor table? Is there a
CODE field in WOLabor, or some other field?

I think you just need to open the Totals query in design view; add the
LaborLink table to the grid; join it on the appropriate field,
whatever that might be. If there are records which do not have a
LaborLink Comment but you want to see them anyway, select the Join
line and choose the option

SHOW ALL Records IN WOLabor AND MATCHING RECORDS IN LaborLink

In the SQL this will be a LEFT JOIN instead of the default INNER JOIN.

John W. Vinson[MVP]

 

[John Vinson]

The two tables are not related. There is the source of the problem.

If you don't have any information in the two tables which would
logically allow you to choose which COMMENT goes with which ASSET,
then I cannot imagine any query which would do what you want.

Suppose you have 1000 records in the Assets table (or in your
four-table query), and 400 records in the LaborLink table.

Which record in the LaborLink table goes with the 398th row in your
query? How can you tell?

John W. Vinson[MVP]