Need help regarding compare the results from two queries

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

Guest

Hello,

I need compare the results of two queries, actually the returned records(one
query will return records with the column "ProductPartID" & "UnitsOnHand"
which representing the number of units we have in stock in the inventory
database for a specific product, the other query will return "ProductPartID"
& "UnitsNeedToUse", which is the number of units we need take out from the
inventory database this time for a specific product), from either of the
query will be a set of records but not only one record. Of course the whole
purpose of module is going to return the value of "Results" as True or false
under the condition if "UnitsOnHand">"UnitsNeedToUse".

I am just wondering whether I need to store the results into two dynamic
array to compare the corresponding fields, and it sounds crazy if using this
method.

Or use other methods(SQL query, or recordset) to achieve the goal. But how
can I compare the results through a statment which I do not know exactly?
something like:
iif([UnitsOnHand] >= [UnitsNeedToUse], "result = True", "result = False")
where
Query1.ProductPartID = Query2.ProductPartID

I need your help for which programing architecture I should use and what's
that? I mean the exactly functions or statement I should look for, could you
give me more details as possible? Or is there any article you knew which
provide the solutions for my particular case?

Thanks in advance!!

Jess
 
Jessica,

Are the two queries based on the same table, or different tables? What will
you do with the results? The reason I am asking, is that it may be possible
to do this with one query that would pull the data that would have an
expression in it that would return True/False. The recordset created from
that query would then show you the result you needed.
 
First, Thanks for your reply !

Actually the two queries are based on several tables like following:

---------------------
1. AssemblyParts Table: Assembly Parts Table

AssemblyPartID(Primary key)
Name
Description
InputDate
CompleteBy
Notes
Lock

------------------------
2. AssemblyDetails Table: one assembly parts may consist of many product
parts, and one product part could belong to many assembly parts

ID ( Autonumber)
AssemblyPartID (foreign key, many-to-one related to AssemblyParts Table)
ProductPartID (foreign key, many-to-one related to Products Table)
Quantity (number of Products need to comprise a assemblypartID)

-------------------------
3. Products Table: the raw material as product parts in the inventory database

ProductPartID (Primary key, one-to-many related to AssemblyDetails table and
one-to-many related to InventoryTransactions Table)
ProductName
Description
.....
---------------------------
4. InventoryTransactions Table: all the transactions including parts
ordered, received and released

TransactionID (autonumber)
TransactionDate
ProductPartID (foreign key, many-to-one related to Products table)
.....
UnitsOrdered (number of units/product parts ordered from a Purchase Order)
UnitsReceived (number of units/product parts received)
UnitsReleased (number of units/product parts need to release from the
inventory database)
------------------------------------------------

My plan is first query will be generated based on two Table "AssemblyParts"
AssemblyDetails" based on known parameter as "AssemblyPartID" and it will be
like:

Select ProductPartID, Quantity from [AssemblyDetails] where [AssemblyPartID
= Me.AssemblyPartID]

the secondary query (more complex, seems like a outjoiner SQL) will be
generated based on first query but go further with "Products"(I am not sure
whether I need include "Products" table in the middle indeed, becuase the
relation of "AssemblyDetails" and "InventoryTransactions" table is actually
many-to-many) & "InventoryTransaction" table to get "UnitsOnHand" which will
be a calcuation result from
[UnitsReceived - UnitsReleased], something like:

Select ProductPartID, [UnitsReceived - UnitsReleased] as UnitsOnHand from
[InventoryTransactions] where ProductPartID in [Select ProductPartID,
Quantity from [AssemblyDetails] where [AssemblyPartID = Me.AssemblyPartID]]

Then, I need compare the "Quantity" and [UnitsOnHand] where their
ProductPartID is identical to determin whether we have enough number of
units/product parts on hand to use for setting up an assembly parts. Finally,
in a function there should be True/False returned to decide whether "Release"
Action can go.

Any suggestion to simply my ideas or the whole query structures? Could you
please provide more details if possible. I am sorry if my second query syntax
is wrong.

Jessica

Klatuu said:
Jessica,

Are the two queries based on the same table, or different tables? What will
you do with the results? The reason I am asking, is that it may be possible
to do this with one query that would pull the data that would have an
expression in it that would return True/False. The recordset created from
that query would then show you the result you needed.

jessica said:
Hello,

I need compare the results of two queries, actually the returned records(one
query will return records with the column "ProductPartID" & "UnitsOnHand"
which representing the number of units we have in stock in the inventory
database for a specific product, the other query will return "ProductPartID"
& "UnitsNeedToUse", which is the number of units we need take out from the
inventory database this time for a specific product), from either of the
query will be a set of records but not only one record. Of course the whole
purpose of module is going to return the value of "Results" as True or false
under the condition if "UnitsOnHand">"UnitsNeedToUse".

I am just wondering whether I need to store the results into two dynamic
array to compare the corresponding fields, and it sounds crazy if using this
method.

Or use other methods(SQL query, or recordset) to achieve the goal. But how
can I compare the results through a statment which I do not know exactly?
something like:
iif([UnitsOnHand] >= [UnitsNeedToUse], "result = True", "result = False")
where
Query1.ProductPartID = Query2.ProductPartID

I need your help for which programing architecture I should use and what's
that? I mean the exactly functions or statement I should look for, could you
give me more details as possible? Or is there any article you knew which
provide the solutions for my particular case?

Thanks in advance!!

Jess
Click to expand...
Click to expand...
 
First, Thanks for your reply !

Actually the two queries are based on several tables like following:

---------------------
1. AssemblyParts Table: Assembly Parts Table

AssemblyPartID(Primary key)
Name
Description
InputDate
CompleteBy
Notes
Lock

------------------------
2. AssemblyDetails Table: one assembly parts may consist of many product
parts, and one product part could belong to many assembly parts

ID ( Autonumber)
AssemblyPartID (foreign key, many-to-one related to AssemblyParts Table)
ProductPartID (foreign key, many-to-one related to Products Table)
Quantity (number of Products need to comprise a assemblypartID)

-------------------------
3. Products Table: the raw material as product parts in the inventory database

ProductPartID (Primary key, one-to-many related to AssemblyDetails table and
one-to-many related to InventoryTransactions Table)
ProductName
Description
.....
---------------------------
4. InventoryTransactions Table: all the transactions including parts
ordered, received and released

TransactionID (autonumber)
TransactionDate
ProductPartID (foreign key, many-to-one related to Products table)
.....
UnitsOrdered (number of units/product parts ordered from a Purchase Order)
UnitsReceived (number of units/product parts received)
UnitsReleased (number of units/product parts need to release from the
inventory database)
------------------------------------------------

My plan is first query will be generated based on two Table "AssemblyParts"
AssemblyDetails" based on known parameter as "AssemblyPartID" and it will be
like
Select ProductPartID, Quantity from [AssemblyDetails] where [AssemblyPartID
= Me.AssemblyPartID]

the secondary query (more complex, seems like a outjoiner SQL) will be
generated based on first query but go further with "Products"(I am not sure
whether I need include "Products" table in the middle indeed, becuase the
relation of "AssemblyDetails" and "InventoryTransactions" table is actually
many-to-many) & "InventoryTransaction" table to get "UnitsOnHand" which will
be a calcuation result from
[UnitsReceived - UnitsReleased], something like:

Select ProductPartID, [UnitsReceived - UnitsReleased] as UnitsOnHand from
[InventoryTransactions] where ProductPartID in [Select ProductPartID,
Quantity from [AssemblyDetails] where [AssemblyPartID = Me.AssemblyPartID]]

Then, I need compare the "Quantity" and [UnitsOnHand] where their
ProductPartID is identical to determin whether we have enough number of
units/product parts on hand to use for setting up an assembly parts. Finally,
in a function there should be True/False returned to decide whether "Release"
Action can go, or we need order more units to make it work.

Any suggestion to simply my ideas or the whole query structures? Could you
please provide more details if possible. I am sorry if my second query syntax
is wrong.

Regards,

Jessica

Klatuu said:
Jessica,

Are the two queries based on the same table, or different tables? What will
you do with the results? The reason I am asking, is that it may be possible
to do this with one query that would pull the data that would have an
expression in it that would return True/False. The recordset created from
that query would then show you the result you needed.

jessica said:
Hello,

I need compare the results of two queries, actually the returned records(one
query will return records with the column "ProductPartID" & "UnitsOnHand"
which representing the number of units we have in stock in the inventory
database for a specific product, the other query will return "ProductPartID"
& "UnitsNeedToUse", which is the number of units we need take out from the
inventory database this time for a specific product), from either of the
query will be a set of records but not only one record. Of course the whole
purpose of module is going to return the value of "Results" as True or false
under the condition if "UnitsOnHand">"UnitsNeedToUse".

I am just wondering whether I need to store the results into two dynamic
array to compare the corresponding fields, and it sounds crazy if using this
method.

Or use other methods(SQL query, or recordset) to achieve the goal. But how
can I compare the results through a statment which I do not know exactly?
something like:
iif([UnitsOnHand] >= [UnitsNeedToUse], "result = True", "result = False")
where
Query1.ProductPartID = Query2.ProductPartID

I need your help for which programing architecture I should use and what's
that? I mean the exactly functions or statement I should look for, could you
give me more details as possible? Or is there any article you knew which
provide the solutions for my particular case?

Thanks in advance!!

Jess
Click to expand...
Click to expand...
 
I think all you really need to do the compare is the assembly detail table
and the transaction table.
Order the assembly detail table by AssemblyPartID and ProductPartId as your
primary table. The join the transaction table by ProductPartiID. You will
then have all the data you need to make the calculation. If this has to have
descriptions, etc, you may have to join the tables to give you additional
display info.

jessica said:
First, Thanks for your reply !

Actually the two queries are based on several tables like following:

---------------------
1. AssemblyParts Table: Assembly Parts Table

AssemblyPartID(Primary key)
Name
Description
InputDate
CompleteBy
Notes
Lock

------------------------
2. AssemblyDetails Table: one assembly parts may consist of many product
parts, and one product part could belong to many assembly parts

ID ( Autonumber)
AssemblyPartID (foreign key, many-to-one related to AssemblyParts Table)
ProductPartID (foreign key, many-to-one related to Products Table)
Quantity (number of Products need to comprise a assemblypartID)

-------------------------
3. Products Table: the raw material as product parts in the inventory database

ProductPartID (Primary key, one-to-many related to AssemblyDetails table and
one-to-many related to InventoryTransactions Table)
ProductName
Description
....
---------------------------
4. InventoryTransactions Table: all the transactions including parts
ordered, received and released

TransactionID (autonumber)
TransactionDate
ProductPartID (foreign key, many-to-one related to Products table)
....
UnitsOrdered (number of units/product parts ordered from a Purchase Order)
UnitsReceived (number of units/product parts received)
UnitsReleased (number of units/product parts need to release from the
inventory database)
------------------------------------------------

My plan is first query will be generated based on two Table "AssemblyParts"
AssemblyDetails" based on known parameter as "AssemblyPartID" and it will be
like:

Select ProductPartID, Quantity from [AssemblyDetails] where [AssemblyPartID
= Me.AssemblyPartID]

the secondary query (more complex, seems like a outjoiner SQL) will be
generated based on first query but go further with "Products"(I am not sure
whether I need include "Products" table in the middle indeed, becuase the
relation of "AssemblyDetails" and "InventoryTransactions" table is actually
many-to-many) & "InventoryTransaction" table to get "UnitsOnHand" which will
be a calcuation result from
[UnitsReceived - UnitsReleased], something like:

Select ProductPartID, [UnitsReceived - UnitsReleased] as UnitsOnHand from
[InventoryTransactions] where ProductPartID in [Select ProductPartID,
Quantity from [AssemblyDetails] where [AssemblyPartID = Me.AssemblyPartID]]

Then, I need compare the "Quantity" and [UnitsOnHand] where their
ProductPartID is identical to determin whether we have enough number of
units/product parts on hand to use for setting up an assembly parts. Finally,
in a function there should be True/False returned to decide whether "Release"
Action can go.

Any suggestion to simply my ideas or the whole query structures? Could you
please provide more details if possible. I am sorry if my second query syntax
is wrong.

Jessica

Klatuu said:
Jessica,

Are the two queries based on the same table, or different tables? What will
you do with the results? The reason I am asking, is that it may be possible
to do this with one query that would pull the data that would have an
expression in it that would return True/False. The recordset created from
that query would then show you the result you needed.

jessica said:
Hello,

I need compare the results of two queries, actually the returned records(one
query will return records with the column "ProductPartID" & "UnitsOnHand"
which representing the number of units we have in stock in the inventory
database for a specific product, the other query will return "ProductPartID"
& "UnitsNeedToUse", which is the number of units we need take out from the
inventory database this time for a specific product), from either of the
query will be a set of records but not only one record. Of course the whole
purpose of module is going to return the value of "Results" as True or false
under the condition if "UnitsOnHand">"UnitsNeedToUse".

I am just wondering whether I need to store the results into two dynamic
array to compare the corresponding fields, and it sounds crazy if using this
method.

Or use other methods(SQL query, or recordset) to achieve the goal. But how
can I compare the results through a statment which I do not know exactly?
something like:
iif([UnitsOnHand] >= [UnitsNeedToUse], "result = True", "result = False")
where
Query1.ProductPartID = Query2.ProductPartID

I need your help for which programing architecture I should use and what's
that? I mean the exactly functions or statement I should look for, could you
give me more details as possible? Or is there any article you knew which
provide the solutions for my particular case?

Thanks in advance!!

Jess
Click to expand...
Click to expand...
Click to expand...
 
I think your idea is great! It will simply my query as you indicated.
However, can you tell me how I can compare the records from the two queries.
I mean which function I should use? Thanks!

Klatuu said:
I think all you really need to do the compare is the assembly detail table
and the transaction table.
Order the assembly detail table by AssemblyPartID and ProductPartId as your
primary table. The join the transaction table by ProductPartiID. You will
then have all the data you need to make the calculation. If this has to have
descriptions, etc, you may have to join the tables to give you additional
display info.

jessica said:
First, Thanks for your reply !

Actually the two queries are based on several tables like following:

---------------------
1. AssemblyParts Table: Assembly Parts Table

AssemblyPartID(Primary key)
Name
Description
InputDate
CompleteBy
Notes
Lock

------------------------
2. AssemblyDetails Table: one assembly parts may consist of many product
parts, and one product part could belong to many assembly parts

ID ( Autonumber)
AssemblyPartID (foreign key, many-to-one related to AssemblyParts Table)
ProductPartID (foreign key, many-to-one related to Products Table)
Quantity (number of Products need to comprise a assemblypartID)

-------------------------
3. Products Table: the raw material as product parts in the inventory database

ProductPartID (Primary key, one-to-many related to AssemblyDetails table and
one-to-many related to InventoryTransactions Table)
ProductName
Description
....
---------------------------
4. InventoryTransactions Table: all the transactions including parts
ordered, received and released

TransactionID (autonumber)
TransactionDate
ProductPartID (foreign key, many-to-one related to Products table)
....
UnitsOrdered (number of units/product parts ordered from a Purchase Order)
UnitsReceived (number of units/product parts received)
UnitsReleased (number of units/product parts need to release from the
inventory database)
------------------------------------------------

My plan is first query will be generated based on two Table "AssemblyParts"
AssemblyDetails" based on known parameter as "AssemblyPartID" and it will be
like:

Select ProductPartID, Quantity from [AssemblyDetails] where [AssemblyPartID
= Me.AssemblyPartID]

the secondary query (more complex, seems like a outjoiner SQL) will be
generated based on first query but go further with "Products"(I am not sure
whether I need include "Products" table in the middle indeed, becuase the
relation of "AssemblyDetails" and "InventoryTransactions" table is actually
many-to-many) & "InventoryTransaction" table to get "UnitsOnHand" which will
be a calcuation result from
[UnitsReceived - UnitsReleased], something like:

Select ProductPartID, [UnitsReceived - UnitsReleased] as UnitsOnHand from
[InventoryTransactions] where ProductPartID in [Select ProductPartID,
Quantity from [AssemblyDetails] where [AssemblyPartID = Me.AssemblyPartID]]

Then, I need compare the "Quantity" and [UnitsOnHand] where their
ProductPartID is identical to determin whether we have enough number of
units/product parts on hand to use for setting up an assembly parts. Finally,
in a function there should be True/False returned to decide whether "Release"
Action can go.

Any suggestion to simply my ideas or the whole query structures? Could you
please provide more details if possible. I am sorry if my second query syntax
is wrong.

Jessica

Klatuu said:
Jessica,

Are the two queries based on the same table, or different tables? What will
you do with the results? The reason I am asking, is that it may be possible
to do this with one query that would pull the data that would have an
expression in it that would return True/False. The recordset created from
that query would then show you the result you needed.

:

Hello,

I need compare the results of two queries, actually the returned records(one
query will return records with the column "ProductPartID" & "UnitsOnHand"
which representing the number of units we have in stock in the inventory
database for a specific product, the other query will return "ProductPartID"
& "UnitsNeedToUse", which is the number of units we need take out from the
inventory database this time for a specific product), from either of the
query will be a set of records but not only one record. Of course the whole
purpose of module is going to return the value of "Results" as True or false
under the condition if "UnitsOnHand">"UnitsNeedToUse".

I am just wondering whether I need to store the results into two dynamic
array to compare the corresponding fields, and it sounds crazy if using this
method.

Or use other methods(SQL query, or recordset) to achieve the goal. But how
can I compare the results through a statment which I do not know exactly?
something like:
iif([UnitsOnHand] >= [UnitsNeedToUse], "result = True", "result = False")
where
Query1.ProductPartID = Query2.ProductPartID

I need your help for which programing architecture I should use and what's
that? I mean the exactly functions or statement I should look for, could you
give me more details as possible? Or is there any article you knew which
provide the solutions for my particular case?

Thanks in advance!!

Jess
Click to expand...
Click to expand...
Click to expand...
 
Actually, this is just one query. To get the result you want, add a column to
the query with any old name:

AnyOldName: [UnitsOnHand] > [UnitsNeedToUse]
This will return the True/False you want.


jessica said:
I think your idea is great! It will simply my query as you indicated.
However, can you tell me how I can compare the records from the two queries.
I mean which function I should use? Thanks!

Klatuu said:
I think all you really need to do the compare is the assembly detail table
and the transaction table.
Order the assembly detail table by AssemblyPartID and ProductPartId as your
primary table. The join the transaction table by ProductPartiID. You will
then have all the data you need to make the calculation. If this has to have
descriptions, etc, you may have to join the tables to give you additional
display info.

jessica said:
First, Thanks for your reply !

Actually the two queries are based on several tables like following:

---------------------
1. AssemblyParts Table: Assembly Parts Table

AssemblyPartID(Primary key)
Name
Description
InputDate
CompleteBy
Notes
Lock

------------------------
2. AssemblyDetails Table: one assembly parts may consist of many product
parts, and one product part could belong to many assembly parts

ID ( Autonumber)
AssemblyPartID (foreign key, many-to-one related to AssemblyParts Table)
ProductPartID (foreign key, many-to-one related to Products Table)
Quantity (number of Products need to comprise a assemblypartID)

-------------------------
3. Products Table: the raw material as product parts in the inventory database

ProductPartID (Primary key, one-to-many related to AssemblyDetails table and
one-to-many related to InventoryTransactions Table)
ProductName
Description
....
---------------------------
4. InventoryTransactions Table: all the transactions including parts
ordered, received and released

TransactionID (autonumber)
TransactionDate
ProductPartID (foreign key, many-to-one related to Products table)
....
UnitsOrdered (number of units/product parts ordered from a Purchase Order)
UnitsReceived (number of units/product parts received)
UnitsReleased (number of units/product parts need to release from the
inventory database)
------------------------------------------------

My plan is first query will be generated based on two Table "AssemblyParts"
AssemblyDetails" based on known parameter as "AssemblyPartID" and it will be
like:

Select ProductPartID, Quantity from [AssemblyDetails] where [AssemblyPartID
= Me.AssemblyPartID]

the secondary query (more complex, seems like a outjoiner SQL) will be
generated based on first query but go further with "Products"(I am not sure
whether I need include "Products" table in the middle indeed, becuase the
relation of "AssemblyDetails" and "InventoryTransactions" table is actually
many-to-many) & "InventoryTransaction" table to get "UnitsOnHand" which will
be a calcuation result from
[UnitsReceived - UnitsReleased], something like:

Select ProductPartID, [UnitsReceived - UnitsReleased] as UnitsOnHand from
[InventoryTransactions] where ProductPartID in [Select ProductPartID,
Quantity from [AssemblyDetails] where [AssemblyPartID = Me.AssemblyPartID]]

Then, I need compare the "Quantity" and [UnitsOnHand] where their
ProductPartID is identical to determin whether we have enough number of
units/product parts on hand to use for setting up an assembly parts. Finally,
in a function there should be True/False returned to decide whether "Release"
Action can go.

Any suggestion to simply my ideas or the whole query structures? Could you
please provide more details if possible. I am sorry if my second query syntax
is wrong.

Jessica

:

Jessica,

Are the two queries based on the same table, or different tables? What will
you do with the results? The reason I am asking, is that it may be possible
to do this with one query that would pull the data that would have an
expression in it that would return True/False. The recordset created from
that query would then show you the result you needed.

:

Hello,

I need compare the results of two queries, actually the returned records(one
query will return records with the column "ProductPartID" & "UnitsOnHand"
which representing the number of units we have in stock in the inventory
database for a specific product, the other query will return "ProductPartID"
& "UnitsNeedToUse", which is the number of units we need take out from the
inventory database this time for a specific product), from either of the
query will be a set of records but not only one record. Of course the whole
purpose of module is going to return the value of "Results" as True or false
under the condition if "UnitsOnHand">"UnitsNeedToUse".

I am just wondering whether I need to store the results into two dynamic
array to compare the corresponding fields, and it sounds crazy if using this
method.

Or use other methods(SQL query, or recordset) to achieve the goal. But how
can I compare the results through a statment which I do not know exactly?
something like:
iif([UnitsOnHand] >= [UnitsNeedToUse], "result = True", "result = False")
where
Query1.ProductPartID = Query2.ProductPartID

I need your help for which programing architecture I should use and what's
that? I mean the exactly functions or statement I should look for, could you
give me more details as possible? Or is there any article you knew which
provide the solutions for my particular case?

Thanks in advance!!

Jess
Click to expand...
Click to expand...
Click to expand...
 
Back
Top