Using Another Table In Where Clause

Feb 13, 2008

This probably sounds stupid but I need to ask because well, I'm stupid. I have created a separate table of partnumbers that DO NOT get included in a SELECT Query for another table (tblmain). How can I use this table in my WHERE clause? Below is my SQL statement and the table with the partnumbers is tblreportpartnumbers.

strSQL = "SELECT SUM([Yield]) AS [My Sum] FROM tblmain"
strSQL = strSQL & " WHERE(([Area]='Cell 1 Supfina' AND "
strSQL = strSQL & "([Date entered]=#" & dtmdate & "#"
strSQL = strSQL & " AND([Part Number]=???????)))"
strSQL = strSQL & " Group By [Date entered];"

Any help would be wonderfulllll

Thanks A lot

View Replies


ADVERTISEMENT

Modules & VBA :: Using Where Clause To Max Of A Table Field

Sep 18, 2014

I am trying to open a form using the where clause to max of a table field.I cannot get the syntax correct.

Code:

DoCmd.OpenForm "frmInput", , , Max(tblClients.ClientID) AS MaxOfClientID FROM tblClients

View 4 Replies View Related

Cannot Reference A Table With Multivalued Field Using IN Clause

Jul 3, 2014

I have an access db that is linked to a sharepoint list (the list originated in Access).I tried a simple select query with parameters to search for by id and another query of the same table to search by date.They both come back with the same error of "cannot reference a table with a multivalued field using an IN clause that refers to another database"Iv'e gone through the table and these fields that the query is using and none seem to have any multivalues.

View 6 Replies View Related

Queries :: How To Join The Table With Query Based On Like Clause

Nov 18, 2014

how I can achieve this in Access

I have a table created in Access- "Master"

FileName Sortorder
ABC_..........4
CDE_..........2
EFG.ss1.......1
GHI.srs........3

I have a Query created in Access whose output is

FileName RowCount Exception RunDate
ABC_20141117.....10...........5........11/17/2014
CDE_11172014......23.........10.......11/17/2014
EFG.ss1................55..........0........11/17/2014
GHI.srs.................15..........5........11/17/2014

Now I require to join these both, the table and the output of the query on the condition where query.fileName like table.fileName.

There is no key in this field. Why I need this because the table has the sort order which the user can change when needed, if I put the sort order in the query then each time there is a change then the query needs to updated which the user can go wrong. Also the filename in the query has date associated which changes every day so I need to pick the unique part of the file name and associate it with the query to get the output from query and sort order from the table.

Required Output:
FileName RowCount Exception RunDate SortOrder
EFG.ss1...............55............0.......11/17/2014...........1
CDE_11172014.....23...........10......11/17/2014......... 2
GHI.srs................15............5.......11/17/2014..........3
ABC_20141117.......10...........5......11/17/2014..........4

View 10 Replies View Related

Syntax Error In CONSTRAINT Clause In MAKE TABLE Query

Apr 6, 2008

Can anybody help me understand why I get the error
"Syntax error in CONSTRAINT clause"?

I get it in Access 2003 and Access 2007.
Both are clean installs with no add-ins

Running this code in VBA, or pasting the SQL directly in a query results in the same error.


Sub test()
sTableName = "Test"

sSQL = "CREATE TABLE " & sTableName & "_Config (" _
& "[idConfig] Int Primary Key," _
& "[Config] Memo," _
& "[Instrument] int," _
& "[Serial_No] Text(25)," _
& "[Firmware] int," _
& "[Orientation] int," _
& "[Sensors] int," _
& "[Sensor_Size] float," _
& "[Sensor_1_Distance] float)"

DoCmd.RunSQL sSQL



sSQL = "CREATE TABLE " & sTableName & "_Leader (" _
& "[idLeader] Int Primary Key," _
& "[idGroup] int," _
& "[idFile] int," _
& "[idConfig] int," _
& "[DateTime] DateTime," _
& "[Heading] float, [Pitch] float, [Roll] float," _
& "[Pressure] float, [Depth_BSL] float, [Height_ASB] float,"
_
& "[Min_Valid_Sensor] int, [Max_Valid_Sensor] int," _
& "[ASM_Bed_Level] float," _
& "CONSTRAINT [FK_Test_Leader_idConfig] FOREIGN KEY ([idConfig]) REFERENCES [Test_Config] ([idConfig]) ON DELETE CASCADE ON UPDATE CASCADE" _
& ")"

DoCmd.RunSQL sSQL

End Sub


The first table is created just fine.
And if I leave the constraint clause out, the second table is created also.
If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.

I have looked up the clause in Access help and even using their example, i get the same error.


Any help will surely be appreciated!

View 2 Replies View Related

Queries :: SELECT Records From A Table Based On IN Clause And Sort Them In Order

Jan 4, 2014

WinXPPro Sp3
Access 2007

After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.

Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)

Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus

List_Order
3
1
15
5
4
12
2
7

View 3 Replies View Related

Help With WHERE Clause

Oct 2, 2005

If this were a table

Amount Job_id Crew_id
$100 1 3
$200 2 4
$400 2 4
$600 2 3
$750 2 3
$800 3 7

and i needed a return (WHERE clause) when job_id is the same (i.e 2), and the crew_id is repeated like 4,4 and 33, how would i write it. I am trying to only sum the rows when this condition is fulfilled. Can anyoen help me

View 1 Replies View Related

Where Clause

May 29, 2006

Could anyone explain me the following sysntax :
... where ((cond1 and cond2) and (fld1<>'54')<>'60')
the syntax did not return any error while executing the query.

Thanks in adv.
nils

View 2 Replies View Related

Where Clause

Jul 26, 2006

I have an select statment that pulls data from a server. This server is really slow, and there is quite a bit of data. This statement has the following where clause.

"WHERE upc_number = '" & strPikWhere & "' AND ((dbo_root_description_type.description_name)='PFM S' Or " & _

"(dbo_root_description_type.description_name)='Full tech') AND ((dbo_root_price.zone) Not Like '8*') " & _

I was wondering if the order of these where statements would make a difference, and if so how?

View 1 Replies View Related

The Between Clause

Dec 5, 2006

Hi,

I have a query using the between clause with dates #01/01/2006# and #01/01/2007#. I have been reading a little on the between clause and it differs from SQL program to SQL program. What are its characteristics in MS Access is it >=#01/01/2006# AND <=#01/01/2007# or >=#01/01/2006# AND <#01/01/2007#.


Thank you.

View 4 Replies View Related

Where Clause

Feb 12, 2008

Where = "cardnumber = " & CardNumberGlobal

DoCmd.OpenForm "scoreform", acNormal, , Where, acFormAdd, acWindowNormal

When I do a Debug Print for where I get cardnumber = 1111111
The card number is a string because it might have a letter in it
don't i need it to be cardnumber = "1111111"

if so how do I get the quotes around the numbers

View 1 Replies View Related

Using An If Clause

Dec 1, 2004

I have a table "addresses" that has the following fields.......
id - type - street1 - street2

and type can be one of the follwoing
graduation,home,work

I am trying to write a query that gives the graduation address if it exists or if graduation doesnt exist it gives the home address.....
I was thinking of an If clause but do not know how to do it....
can i write a module...???
Any help on this is appreciated.....
Any other method also can be suggested....

View 1 Replies View Related

Calculated Value In Where Clause

Sep 30, 2005

In one of my queries, I'd like to reuse a calculated field as
criteria in the Where clause. SQL seems reluctant to want to
do this. You can RESTATE the calculation in the Where clause,
but can't refer to it by Name. You can Order by it, but it
can't be in the Where clause.

If the query result set consists on a set of Named columns, why
can you refer to a calculated column by Name in the "Order By"
clause but not in the Where clause?


Why is this OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference <-- Just a calculated field
From SomeTable

And this is OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Order By TheDifference <-- Order By is OK

And this in not OK?

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Where TheDifference > 100 <-- Can't use TheDifference in Where clause

Don't want to repeat the calculation.

Select FldA As FirstField,
FldB As SecondField,
FldA - FldB As TheDifference
From SomeTable
Where FldA - FldB > 100 <-- I know this works, but ...
Order By TheDifference



Wayne

View 3 Replies View Related

Having Or Group Clause

Jun 2, 2006

i have to list BookID numbers borrowed by patrons where fine are greater than a set amount

View 1 Replies View Related

Searching With A Like Clause

Jul 12, 2007

hi, is it possible to have a form where you enter a word in a text box and it searchs a table and brings up all records which match it, but it doesnt have to be an exact match.

e.g.
you enter : the

results : the car
the bike
etc.

any help appreciated.

jjames

View 1 Replies View Related

Order By Clause Not Being Following

Jul 18, 2007

Hi

I have a problem with an Order By Clause.

Basically, my database holds records of sites in the UK, and I have a select query (qry_SumGIS3) that opens up a summary report (rptSumGIS3) when a button is clicked on the menu page. The report opens perfectly, showing me all the information I need to know about all the sites in my database. However, it is ignoring the Order By Clause I have specifically told it to do. I want to order the records that appear in the report by a calculated field (OBJ_WS_OVERALL_CALC) in descending order (highest to lowest percentage). Instead, it is ordering it by the site id (RTP_ID). How can I stop it doing this?

This is my code (that was created in the SQL view of the query design):
"SELECT MEASURE1.OBJ_WS_OVERALL_CALC, [Tab 1: Factual].RTP_ID, OBJECTIVES.OBJ_ID, OBJECTIVES.OBJ_NAME, MEASURE1.MEASURE_ID, MEASURE.MEASURE_NAME, MEASURE1.MEASURE_SCORE, MEASURE1.MEASURE_WEIGHT
FROM [Tab 1: Factual] INNER JOIN (OBJECTIVES INNER JOIN (MEASURE1 INNER JOIN MEASURE ON MEASURE1.MEASURE_ID = MEASURE.MEASURE_ID) ON OBJECTIVES.OBJ_ID = MEASURE.OBJ_ID) ON [Tab 1: Factual].RTP_ID = MEASURE1.RTP_ID
ORDER BY MEASURE1.OBJ_WS_OVERALL_CALC;"

The red line is the part of the code that I think is being ignored.

Anyone that can help me is a lifesaver!

View 1 Replies View Related

WHERE Clause In SQL String

Sep 3, 2007

Hi all, do you think you could help me with this SQL statement I'm using in vba code for a recordset object?

strSQL = "SELECT idCompactPartNumber, txtItem," & _
"lngDepartmentNumber , dteAquiredDate, curPrice," & _
"intQuantity , lngUnit, txtLocation," & _
"imgPicture, dteRetiredDate, blnActive," & _
"blnConsumables " & _
"FROM tblMaterials" & _
"WHERE lngDepartmentNumber=" & lngDepartment

When I try to open the recordset with this, it says there is an error in the WHERE clause. I've tried changing all sorts of things but nothing works. I've tried with the WHERE clause commented out and it all works fine then.

Hope you can help, thanks

View 5 Replies View Related

WHERE CLAUSE In SELECT

Oct 24, 2007

Hi Everyone,

I have the following SQL statement. The COUNT(b.BookingID) AS TB, gives me the number of bookings. I need to ensure that the number of bookings + PrebookCapacity is less than v.Capacity. I do this by:

COUNT(b.BookingID) AS TB + PrebookCapacity < v.Capacity in the WHERE CLAUSE. But when I run this in MS Access 2003, it says Enter Parameter Value for b.TB (It does not recognise b.TB as b.TB is not a table field). If I omit AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0)), from the WHERE clause, it works fine, also giving me the TB.

As I need to do my check in the WHERE clause, how can I check to make sure that the COUNT(b.BookingID) AS TB + PrebookCapacity is < v.Capacity, without any problems.

SELECT CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM") AS [DateTime], s.SessionID, PrebookCapacity, v.Capacity,
COUNT(b.BookingID) AS TB
FROM tblCourses AS c, tblSessions AS s, tblVenues AS v, tblbookings AS b, qrySessionsAccepted AS q
WHERE c.CourseID = s.CourseID AND s.SessionID = 85 AND q.SessionID = s.SessionID AND b.SessionID = s.SessionID AND v.VenueID = s.VenueID
AND ((b.TB + PrebookCapacity < v.Capacity AND SessionStatus = 1) OR (b.TB < v.Capacity AND SessionStatus = 0))
GROUP BY CourseName, Format([s.SessionDate]+[s.StartTime],"dd/mmm/yyyy hh:ss AM/PM") & " - " & Format([s.EndTime],"hh:ss AM/PM"), s.SessionID, PrebookCapacity, v.Capacity;

Thanks,

Bash.

View 8 Replies View Related

Where Clause Issue

Dec 19, 2007

Hi I'm new to query writing and I'm needing help with a query that will search a field that either contains a name or an address and only return fields with addresses.

Select address1
From Booktable,
Where (Left([address1],1) = Like "[A-Z]"


Thanks in advance for any help!:D

View 2 Replies View Related

Parameter With IN Clause

Feb 7, 2008

in simple my query looks like

SELECT Field1, Field2
from table1
where table1.Field2 in (1,2,3)

and now i need to parameterize this query.... I tried with

SELECT Field1, Field2
from table1
where table1.Field2 in ([Parameter])

Now the problem is... it is working fine with only one value
For example if I input 1, it provide the required result. however, if I input 1,2 it doesnt provide a single recordset

I changed the datatype of parameter from number to text and also tried different way of inputting number like "1,2" , 1,2, but still problem is the same...

Can someone please guide me where I am going wrong

Thanks in advance

View 1 Replies View Related

Iif Statements... Then The WHERE Clause? Help!

Apr 16, 2008

Hi,
here is my query which runs within the VB code...

queryString2 = ""
queryString2 = queryString2 & " INSERT INTO markup SELECT TOP 1 JDE_Customer_No AS [JDE Customer No],"
queryString2 = queryString2 & " Custom2.Customer_Name AS [Customer Name],"
queryString2 = queryString2 & " part.id AS Part,"
queryString2 = queryString2 & " price_factor.use_fixed_price AS [Use_fixed],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.price,price_list.price) AS [Base Price],"
queryString2 = queryString2 & " iif(Use_fixed =1,price_factor.fixed_price_factor,price_factor.fa ctor) AS [Factor],"
queryString2 = queryString2 & " iif(Use_fixed =1,fixed_price_list.min_quantity,price_list.min_qu antity) AS [Price Break],"
queryString2 = queryString2 & " part_select.quantity AS [Quantity Required],"
queryString2 = queryString2 & " part.unit_id As [Unit],"
queryString2 = queryString2 & " iif(Use_fixed =1"
queryString2 = queryString2 & " ,(price_factor.fixed_price_factor*(fixed_price_lis t.price*FixedCurrencyConUKP.sterling_exchange_rate ))"
queryString2 = queryString2 & " ,(price_factor.factor *(price_list.price*CurrencyConUKP.sterling_exchang e_rate))) AS Price"
queryString2 = queryString2 & " FROM part_select, part, fixed_price_list, price_list, price_factor, Custom2, CurrencyConUKP, FixedCurrencyConUKP"
queryString2 = queryString2 & " WHERE part.id = part_select.part"
queryString2 = queryString2 & " AND price_list.part_id = part.id"
queryString2 = queryString2 & " AND fixed_price_list.part_id = part.id"
queryString2 = queryString2 & " AND Custom2.JDE_Price_Group=price_factor.customer_grou p"
queryString2 = queryString2 & " AND price_factor.part_group=part." & PriceGroup
queryString2 = queryString2 & " AND Custom2.Customer_Name = " & "'" & CustomerSelection & "'"
queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND part.id = " & "'" & select_part_rst!part & "'"
queryString2 = queryString2 & " AND part_select.quantity = " & select_part_rst!quantity
queryString2 = queryString2 & " ;"

It all works pretty much fine, however, the problem occurs because the iif statement tells the code to use either the fixed_price_list table or the price_list table depending on the value of Use_fixed. the iif works fine, its the WHERE clause. because the iif statements tell the query to look at one table or the other, the WHERE clause includes statements from both tables e.g.

queryString2 = queryString2 & " AND price_list.min_quantity <= part_select.quantity "
queryString2 = queryString2 & " AND fixed_price_list.min_quantity <= part_select.quantity "

but some parts dont have information within the fixed_price_list table, and thus are not being found in the results due to the WHERE clause. can anyone help?? i tried using a left join but it didnt work how i wanted. in a perfect world i would be able to include a iif statement in the where clause e.g.

queryString2 = queryString2 & " iif Use_fixed =1, AND price_list.min_quantity <= part_select.quantity, AND fixed_price_list.min_quantity <= part_select.quantity) "

can anyone help?

View 5 Replies View Related

Where Clause Trouble

Apr 22, 2008

OK I have code to make a where clause this is working fine. Now I have to add another piece of code to filter it even more, this is where I am having trouble

Code that works fine

'construct a where clause as required for the list box
'if the Manufactur box is blank then all games will show up on the list
If Mfg = "0" Then Mfg = "*"
If Denom <> "" Then where = "DenomFix LIKE '" & Denom & "*' AND "
If Mfg <> "" Then where = where & "MFRCode LIKE '" & Mfg & "' AND "
If Search <> "" Then where = where & "Description LIKE '*" & Search & "*' AND "
'this will filter the list with only the approved games
Dim Aprvd As String
Aprvd = "Approved"
If Me.AprrovedCheck = True Then where = where & "Approved LIKE'" & Aprvd & "' AND "

'remove the trailing "AND ", if it exists
If Right(where, 4) = "AND " Then where = Left(where, Len(where) - 4)
'and if there is any text in the where clause, add the word 'WHERE '
If where <> "" Then where = "WHERE " & where
'apply the SQL to the rowsource of the List box to Pick a game

Me.List8.RowSource = _
"SELECT Approved, ReelStops As [Corp ID], DenomFix as Denom, Description As Theme, Par, MaxCoins As [Max Coin], PayLines As [Pay Lines]" & _
"FROM MachineTypeQuery " & _
where & _
"ORDER BY Description;"

Now what I need to add code to make a where clause that says something like DVIA is greater then 0 (DVIA >0) but I want to do this by its self and add it back into the where clause

Example
Private Sub ApplyPropertyWhere()
If PropertyGlobal = "DVIA" Then PropertyWhereGlobal = "DVIA > 0"

I would then put a line of code in the where clause like this
'This will filter the list with only the property they are from
If Me.PropertyCheck = True Then where = where & PropertyWhereGlobal

Please Help me fix the "DVIA >0" Thanks

View 9 Replies View Related

Using A Where Clause On A Subform??

Oct 4, 2005

Hi,

I have a sub form on my form which displays the normalised data on a datasheet.

I have managed to create a txt box on my main form that contains the number of records within that subform by doing this in the control source.

ControlSource --> =subform_controlname.form.recordset.recordcount


How do i know create another text box that display the number of rows within the subform where fielda = "String" (string being a word)????

Thanks
k0r54

View 4 Replies View Related

Where Clause Problem

Apr 7, 2007

First I just designed my first database and all seems to work fine except that my if I try and use a where clause in my query it won't work. Example below:

This query works fine but as soon as I add this it won't work.
SELECT Assets.EmployeeName
FROM Assets


SELECT Assets.EmployeeName
FROM Assets
WHERE Assets.EmployeeName='Lucky Kruse';

If needed I can send the database as I really need help on this.
Thanks for the help.

View 9 Replies View Related

GROUP BY With WHERE Clause

Oct 19, 2013

I have seen WHERE clause works with GROUP BY, just not this one, not sure why.

Code:

SELECT seller, iid, startTIME , count(*) as cntBID
FROM bid
WHERE count(*) = 1
GROUP BY seller, iid, startTIME

Code:
SELECT seller, iid, startTIME , count(*) as cntBID
FROM bid
GROUP BY seller, iid, startTIME
HAVING count(*) = 1

View 4 Replies View Related

Syntax Error In From Clause

Nov 28, 2007

When I'm runnig the query, I'm getting an error "syntax error in from clause".
I can't enter to "design" mode to find the error.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved