need to take out an INNER JOIN to a user table

G

Guest

I am trying to edit the following query. When I open it, Access really
mangles it so that it doesn't even work. This goes to a form in the database
and there are drop down menus for choices. I'm trying to add the parameters
in those lists to run the query. The first time I ran it it asked for the
first parameter I think was curUser. Now it doesn't ask for that anymore but
it does ask for the form drop down fields.
I will try inputing a value in the select query.

The problem is I want the inner join taken out so Access won't keep
destroying the query. It puts paren's around the wrong place and seems like
it takes out a line.
I think the following line could be deleted as well as the inner join to the
qCurrentUser table:
ExportData.eehCurUser=qCurrentUser.curUser

There is a separate query called qCurrentUser.curUser with only the one
field in it.



SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;

Thanks,
 
G

Guest

I thought I did, let me know if this one got mangled by me opening it in
design mode.


SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;


KARL DEWEY said:
Post the complete SQL statement.
--
KARL DEWEY
Build a little - Test a little


Janis said:
I am trying to edit the following query. When I open it, Access really
mangles it so that it doesn't even work. This goes to a form in the database
and there are drop down menus for choices. I'm trying to add the parameters
in those lists to run the query. The first time I ran it it asked for the
first parameter I think was curUser. Now it doesn't ask for that anymore but
it does ask for the form drop down fields.
I will try inputing a value in the select query.

The problem is I want the inner join taken out so Access won't keep
destroying the query. It puts paren's around the wrong place and seems like
it takes out a line.
I think the following line could be deleted as well as the inner join to the
qCurrentUser table:
ExportData.eehCurUser=qCurrentUser.curUser

There is a separate query called qCurrentUser.curUser with only the one
field in it.



SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;

Thanks,
Click to expand...
Click to expand...
 
G

Guest

I went to the database on Terminal SErver and got this SQL statement which is
correct. The other one was on a connected database so it might be wrong
since I opened it several times and Access moved things around. The other
one I just sent is the copy I have connected to the one on the terminal
server:

SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;
tia,

KARL DEWEY said:
Post the complete SQL statement.
--
KARL DEWEY
Build a little - Test a little


Janis said:
I am trying to edit the following query. When I open it, Access really
mangles it so that it doesn't even work. This goes to a form in the database
and there are drop down menus for choices. I'm trying to add the parameters
in those lists to run the query. The first time I ran it it asked for the
first parameter I think was curUser. Now it doesn't ask for that anymore but
it does ask for the form drop down fields.
I will try inputing a value in the select query.

The problem is I want the inner join taken out so Access won't keep
destroying the query. It puts paren's around the wrong place and seems like
it takes out a line.
I think the following line could be deleted as well as the inner join to the
qCurrentUser table:
ExportData.eehCurUser=qCurrentUser.curUser

There is a separate query called qCurrentUser.curUser with only the one
field in it.



SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;

Thanks,
Click to expand...
Click to expand...
 
G

Guest

This is strange. The one I just copied from terminal server I used to create
a new query. It isn't asking for my userID anymore? I did notice on the
terminal server there is a relationship which doesn't show up on my connected
copy. Perhaps that is the problem? That is why it was asking for my curUser.

Janis said:
I went to the database on Terminal SErver and got this SQL statement which is
correct. The other one was on a connected database so it might be wrong
since I opened it several times and Access moved things around. The other
one I just sent is the copy I have connected to the one on the terminal
server:

SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;
tia,

KARL DEWEY said:
Post the complete SQL statement.
--
KARL DEWEY
Build a little - Test a little


Janis said:
I am trying to edit the following query. When I open it, Access really
mangles it so that it doesn't even work. This goes to a form in the database
and there are drop down menus for choices. I'm trying to add the parameters
in those lists to run the query. The first time I ran it it asked for the
first parameter I think was curUser. Now it doesn't ask for that anymore but
it does ask for the form drop down fields.
I will try inputing a value in the select query.

The problem is I want the inner join taken out so Access won't keep
destroying the query. It puts paren's around the wrong place and seems like
it takes out a line.
I think the following line could be deleted as well as the inner join to the
qCurrentUser table:
ExportData.eehCurUser=qCurrentUser.curUser

There is a separate query called qCurrentUser.curUser with only the one
field in it.



SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;

Thanks,
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I thought I found the problem but it Access is still mangling the query when
I open it in design view. In the real query I copied from the server it
didn't make the relationship between qCurUser when I made a new query in my
copy of Access. I tried to create the relationship which I thought might be
broken and I noticed the field eeH_curUser or whatever it was ,wasn't
present. It appears that in my copy of the database the field eehCurUser is
not in the Export table. I copied the exact query from the terminal server
and put it in my copy of the database? The copy of the database is the same
as that on the terminal server. Why is that field missing on my database?
That must be why the query couldn't work.

KARL DEWEY said:
Post the complete SQL statement.
--
KARL DEWEY
Build a little - Test a little


Janis said:
I am trying to edit the following query. When I open it, Access really
mangles it so that it doesn't even work. This goes to a form in the database
and there are drop down menus for choices. I'm trying to add the parameters
in those lists to run the query. The first time I ran it it asked for the
first parameter I think was curUser. Now it doesn't ask for that anymore but
it does ask for the form drop down fields.
I will try inputing a value in the select query.

The problem is I want the inner join taken out so Access won't keep
destroying the query. It puts paren's around the wrong place and seems like
it takes out a line.
I think the following line could be deleted as well as the inner join to the
qCurrentUser table:
ExportData.eehCurUser=qCurrentUser.curUser

There is a separate query called qCurrentUser.curUser with only the one
field in it.



SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;

Thanks,
Click to expand...
Click to expand...
 
G

Guest

Change your join to this ---
FROM ExportData LEFT JOIN qCurrentUser ON ExportData.eehCurUser=
qCurrentUser.CurUser


--
KARL DEWEY
Build a little - Test a little


Janis said:
I thought I found the problem but it Access is still mangling the query when
I open it in design view. In the real query I copied from the server it
didn't make the relationship between qCurUser when I made a new query in my
copy of Access. I tried to create the relationship which I thought might be
broken and I noticed the field eeH_curUser or whatever it was ,wasn't
present. It appears that in my copy of the database the field eehCurUser is
not in the Export table. I copied the exact query from the terminal server
and put it in my copy of the database? The copy of the database is the same
as that on the terminal server. Why is that field missing on my database?
That must be why the query couldn't work.

KARL DEWEY said:
Post the complete SQL statement.
--
KARL DEWEY
Build a little - Test a little


Janis said:
I am trying to edit the following query. When I open it, Access really
mangles it so that it doesn't even work. This goes to a form in the database
and there are drop down menus for choices. I'm trying to add the parameters
in those lists to run the query. The first time I ran it it asked for the
first parameter I think was curUser. Now it doesn't ask for that anymore but
it does ask for the form drop down fields.
I will try inputing a value in the select query.

The problem is I want the inner join taken out so Access won't keep
destroying the query. It puts paren's around the wrong place and seems like
it takes out a line.
I think the following line could be deleted as well as the inner join to the
qCurrentUser table:
ExportData.eehCurUser=qCurrentUser.curUser

There is a separate query called qCurrentUser.curUser with only the one
field in it.



SELECT ExportData.eehCommServed, ExportData.eehNodeNumber,
ExportData.eehSysPrin, ExportData.eehGL, ExportData.eehVirtualNode,
ExportData.eehOldNodeNum, ExportData.eehbusHPDesign,
ExportData.eehbusOpticalTX, ExportData.eehbusOpticalRx,
ExportData.eehServDeliveryType, ExportData.eehCMTS, ExportData.eehUnitID_IP,
ExportData.eehprtSlotNumber, ExportData.eehprtDSPortNum,
ExportData.eehDSFreq, ExportData.eehDownStreamMod,
ExportData.eehprtUSPortNum, IIf([eehPhysicalUSPort]<0,99,[eehPhysicalUSPort])
AS Expr1, ExportData.eehUSFreq, ExportData.eehUSCnnlW,
ExportData.eehprtUSModulation, ExportData.eehServingGroup,
ExportData.eehNCPChanges, ExportData.eehbusHPCSG, " " AS [Node Group],
ExportData.eehDownstreamPlant, ExportData.eehOM, ExportData.eehN,
ExportData.eehEMM, ExportData.eehSlMulticast, ExportData.eehprtModemCount,
ExportData.eehprteMTACount, ExportData.eehExcelExportID
FROM ExportData INNER JOIN qCurrentUser ON
ExportData.eehCurUser=qCurrentUser.CurUser
ORDER BY ExportData.eehCMTS, ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum, Left([eehCMTS],5),
Mid([eehCMTS],9,3), Mid([eehCMTS],6,3), ExportData.eehprtSlotNumber,
ExportData.eehprtDSPortNum, ExportData.eehprtUSPortNum,
ExportData.eehNodeNumber;

Thanks,
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append query with an Inner Join to Destination Table Not working 2
INNER JOIN table to query - need to delete query! 2
How to edit a table accessed thru an Inner Join SQL 4

Top