SQL Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone,

For this application that I am working on, I am using the query below, to
determine when I have reached a quantity limit. E.g. Part 1234 has a QLIMIT
of 5. When I scan the 6th part, I should recieve a warning stating that I
have exceeded the QLIMIT amount.

The problem with this statement is that there is a different QLIMIT for each
part, therefore I cannot use a specific number for [QLIMIT] = .

Is there someway I can modify this statement so that it detects what the
QLIMIT is for each part. The QLIMIT column is in the item master table.

SQLValid = "Select * from [Item Master] where [part #] = " & _
"'" & [Forms]![Actual Stocktake]![YPROD] & _
"' And [QLIMIT]= ?

Thanks
 
You don't say how the query would find out what the correct value of QLIMIT
is -- it it the value in a field in a table? is it a value that is provided
by the user?

If it's the former, use the DLookup function to read the value from the
table.
 
Hi,


If it is acceptable to 'work' under a form, then in the before update event,
you can check, with a DCount, how many records there is actually, and how
max they can be, cancel the update if the limit is already reached.


cancel = DCount("*", "Inventory", "partNumber=[Forms]![Actual
Stocktake]![YPROD]" ) < DLookup("qlimit", "Limits",
"partNumber=[Forms]![Actual Stocktake]![YPROD]"

Otherwise, it is still quite doable, in Jet, with a CHECK constraint, but
there is NO user interface built for them.



--- unchecked-- in the debug window, type something like:

CurrentProject.Connection.Execute "ALTER TABLE inventory ADD CONSTRAINT
noMoreThanAllowed CHECK( (SELECT COUNT(*) FROM inventory AS a WHERE
a.partNumber=partNumber) <= (SELECT MAX(b.qlimit) FROM limits AS b WHERE
b.partNumber=partNumber))"



Note that the MAX is superfluous if there is just one record for each
possible partNumber, but it makes us sure the query return ONE row (and one
value). A check constraint is validated after the record is tentatively
appended, or modified. If the expression passed as argument to the CHECK( )
evaluates to false, the modification (or the append) is (logically) rolled
back. Also, the name of the constraint, here noMoreThanAllowed, MUST be
unique over ALL THE DATABASE. You may add the tables name, if you plan to
have many of these "inter table" CHECK constraints. Finally, MS SQL Server
do not support those CHECK constraint, inter table (or inter records of the
same table), only Jet does.



Hoping it may help,
Vanderghast, Access MVP
 
Hi Ken,

The QLIMIT value is found in the ITEM MASTER table. For each individual part
there is a QLIMIT. E.g. part 1234 has a qlimit of 5, part 2222 has a qlimit
of 2, etc.

I have tried several times using DLookup, can you please provide me with a
working example.

Cheers


Ken Snell (MVP) said:
You don't say how the query would find out what the correct value of QLIMIT
is -- it it the value in a field in a table? is it a value that is provided
by the user?

If it's the former, use the DLookup function to read the value from the
table.
--

Ken Snell
<MS ACCESS MVP>


Jack said:
Hi Everyone,

For this application that I am working on, I am using the query below, to
determine when I have reached a quantity limit. E.g. Part 1234 has a
QLIMIT
of 5. When I scan the 6th part, I should recieve a warning stating that I
have exceeded the QLIMIT amount.

The problem with this statement is that there is a different QLIMIT for
each
part, therefore I cannot use a specific number for [QLIMIT] = .

Is there someway I can modify this statement so that it detects what the
QLIMIT is for each part. The QLIMIT column is in the item master table.

SQLValid = "Select * from [Item Master] where [part #] = " & _
"'" & [Forms]![Actual Stocktake]![YPROD] & _
"' And [QLIMIT]= ?

Thanks
Click to expand...
Click to expand...
 
Here's an example:

SQLValid = "Select * from [Item Master] where [part #] = " & _
"'" & [Forms]![Actual Stocktake]![YPROD] & _
"' And [QLIMIT]=" & DLookup("QLIMIT", "ITEM MASTER", _
"[part #]=' & [Forms]![Actual Stocktake]![YPROD] & "'")

I'm not understanding why you'd want to use QLIMIT as a criterion in the SQL
statement though? This SQL statement would return QLIMIT value as one of the
fields in the query, and you could work with it directly in your form:

SQLValid = "Select * from [Item Master] where [part #] = " & _
"'" & [Forms]![Actual Stocktake]![YPROD] & "'"


--

Ken Snell
<MS ACCESS MVP>



Jack said:
Hi Ken,

The QLIMIT value is found in the ITEM MASTER table. For each individual
part
there is a QLIMIT. E.g. part 1234 has a qlimit of 5, part 2222 has a
qlimit
of 2, etc.

I have tried several times using DLookup, can you please provide me with a
working example.

Cheers


Ken Snell (MVP) said:
You don't say how the query would find out what the correct value of
QLIMIT
is -- it it the value in a field in a table? is it a value that is
provided
by the user?

If it's the former, use the DLookup function to read the value from the
table.
--

Ken Snell
<MS ACCESS MVP>


Jack said:
Hi Everyone,

For this application that I am working on, I am using the query below,
to
determine when I have reached a quantity limit. E.g. Part 1234 has a
QLIMIT
of 5. When I scan the 6th part, I should recieve a warning stating that
I
have exceeded the QLIMIT amount.

The problem with this statement is that there is a different QLIMIT for
each
part, therefore I cannot use a specific number for [QLIMIT] = .

Is there someway I can modify this statement so that it detects what
the
QLIMIT is for each part. The QLIMIT column is in the item master table.

SQLValid = "Select * from [Item Master] where [part #] = " & _
"'" & [Forms]![Actual Stocktake]![YPROD] & _
"' And [QLIMIT]= ?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top