A Continuous form field question

[Aldred]

Hi all,
I had a continuous form like this:

PID: 123
Name: ABC
Sum: unbound

PID: 124
Name: DEF
Sum: unbound

PID and Name was generated from a query. There was a Unbound textbox which
was associated with Sum label.
The data of that field will be generated from a complex query depends on the
PID and other fields which is a sum of some numbers from another table.

The problem is that I do not know how to make my query fires whenever the
PID changes. For example, when the PID is 123, the Sum should be 1000, and
when PID is 124, the Sum should be 2000 something like that.

I tried to make a record set on the events but they don't work. I tried to
put it in Record source and I get an error. I tried to put the query and
save it, then call it out in the record source and still fail. All I get is
unbound, ?Name?, or Error.

Can some one please help me.

Thanks in advance.

 

[aceavl via AccessMonster.com]

what is the query for the PID and Name?
is this query the Record Source for the form?

try putting the PID and Name in your SUM query and use that as
the Record Source for the form.

 

[Marshall Barton]

I had a continuous form like this:

PID: 123
Name: ABC
Sum: unbound

PID: 124
Name: DEF
Sum: unbound

PID and Name was generated from a query. There was a Unbound textbox which
was associated with Sum label.
The data of that field will be generated from a complex query depends on the
PID and other fields which is a sum of some numbers from another table.

The problem is that I do not know how to make my query fires whenever the
PID changes. For example, when the PID is 123, the Sum should be 1000, and
when PID is 124, the Sum should be 2000 something like that.

I tried to make a record set on the events but they don't work. I tried to
put it in Record source and I get an error. I tried to put the query and
save it, then call it out in the record source and still fail. All I get is
unbound, ?Name?, or Error.

For a continuous (or datasheet) form, you need to join your
complex query in the form's record source query. I would
suspect that you can Join on the PID field to pick up the
summed field.

 

[Aldred@office]

Hi,
Here is the queries. Query1 is to fetch the PID and whatever fields
neeeded. Query2 is to calculate the Sum of another table which is depended
on the PID and other fields. I just don't know how to join these 2 queries
together, how may I have little help in this?

Thanks.

Query1:

SELECT PartNumID AS PID, Manu, POOutNum, PartNum, Quantity, DueDate,
tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID;

Query2:

SELECT Sum(tDelivered.DQuantity) AS Delivered
FROM tPOitems, tPOin, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOitems.POinID
And tPOitems.ID=tDelivered.POitemsID
And tDelivered.PartNumID=342;

 

[Aldred@office]

Hi,
Here is the queries. Query1 is to fetch the PID and whatever fields
neeeded. Query2 is to calculate the Sum of another table which is depended
on the PID and other fields. I just don't know how to join these 2 queries
together, how may I have little help in this?

Thanks.

Query1:

SELECT PartNumID AS PID, Manu, POOutNum, PartNum, Quantity, DueDate,
tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID;

Query2:

SELECT Sum(tDelivered.DQuantity) AS Delivered
FROM tPOitems, tPOin, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOitems.POinID
And tPOitems.ID=tDelivered.POitemsID
And tDelivered.PartNumID=342;

 

[Chegu Tom]

What is PartNumID=342; ? Is this something that will change for each
query. Can it be found on your main form?

Hi,
Here is the queries. Query1 is to fetch the PID and whatever fields
neeeded. Query2 is to calculate the Sum of another table which is
depended on the PID and other fields. I just don't know how to join these
2 queries together, how may I have little help in this?

Thanks.

Query1:

SELECT PartNumID AS PID, Manu, POOutNum, PartNum, Quantity, DueDate,
tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID;

Query2:

SELECT Sum(tDelivered.DQuantity) AS Delivered
FROM tPOitems, tPOin, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOitems.POinID
And tPOitems.ID=tDelivered.POitemsID
And tDelivered.PartNumID=342;

 

[Marshall Barton]

Here is the queries. Query1 is to fetch the PID and whatever fields
neeeded. Query2 is to calculate the Sum of another table which is depended
on the PID and other fields. I just don't know how to join these 2 queries
together, how may I have little help in this?

Query1:

SELECT PartNumID AS PID, Manu, POOutNum, PartNum, Quantity, DueDate,
tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID;

Query2:

SELECT Sum(tDelivered.DQuantity) AS Delivered
FROM tPOitems, tPOin, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOitems.POinID
And tPOitems.ID=tDelivered.POitemsID
And tDelivered.PartNumID=342;

Hard to tell witout seeing your tables or atleast having the
table name qualifying each field, but I think maybe you
could get started by trying something like:

Query1:
SELECT PartNumID AS PID, Manu, POOutNum, PartNum, Quantity,
DueDate, tPOItems.DeliveryDate, SpecialInst
FROM tManu, tPOin, tPOItems, tPOOut
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOItems.POinID
And tPOOut.ID=tPOItems.POOutID
And tPOOut.ManuID=tManu.ID;

Query2:
SELECT tDelivered.PartNumID,
Sum(tDelivered.DQuantity) AS Delivered
FROM tPOitems, tPOin, tDelivered
WHERE POinNum=Forms!fReport!POinNumDelivered
And tPOin.ID=tPOitems.POinID
And tPOitems.ID=tDelivered.POitemsID
GROUP BY tDelivered.PartNumID

Query3:
SELECT Query1.*, Query3.Delivered
FROM Query1 INNER JOIN Query2
ON Query1.PID = Query3.PartNumID

If you really have to use the archaic kind of joins, change
the INNER JOIN is used. Otherwise, I suggest that you use
real JOINs instead of the way you posted.

 

[Aldred]

It is nothing, I just hard code it for testing.

What is PartNumID=342; ? Is this something that will change for each
query. Can it be found on your main form?