Show all records from one table

[Guest]

Hey everyone, I have a problem that hopefully can be fixed shortly.

I have a table called "Lines". It only contains one field, which is lines,
and it includes 5 lines: "Line 1", "Line 2", etc.
I also have a "Holds" query that will get all records for a field called
"Holds" for each line. I use the "Holds" query in my query which is having
problems. My query takes the holds from my "Holds" query and sums them up for
each line. However, it will only output a number for those lines that have
had any holds. I would like to show every line, even if it has zero holds. I
have tried to change the relationship to show all values from my "Lines"
table and any that match from my "Holds" query, but have been unsuccessful.
Any help would be greatly appreciated. Thank you.

 

[Guest]

Show us the SQL for what is almost working now. Open the query in design
view. Next go to View, SQL View and copy and past it here.

 

[Guest]

Here it is:

SELECT tblLines.Line, Sum(qrySelectHoldsResults.CurrentHoldQty) AS
SumOfCurrentHoldQty
FROM qrySelectHoldsResults RIGHT JOIN tblLines ON qrySelectHoldsResults.Line
= tblLines.Line
WHERE (((qrySelectHoldsResults.HoldDate) Between
NZ([forms]![Switchboard]![txtStartDate],0) And
NZ([forms]![Switchboard]![txtEndDate],Date())))
GROUP BY tblLines.Line;

 

[Guest]

We probably need the SQL for qrySELECTHoldsResults also.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here it is:

SELECT tblLines.Line, Sum(qrySelectHoldsResults.CurrentHoldQty) AS
SumOfCurrentHoldQty
FROM qrySelectHoldsResults RIGHT JOIN tblLines ON qrySelectHoldsResults.Line
= tblLines.Line
WHERE (((qrySelectHoldsResults.HoldDate) Between
NZ([forms]![Switchboard]![txtStartDate],0) And
NZ([forms]![Switchboard]![txtEndDate],Date())))
GROUP BY tblLines.Line;

Show us the SQL for what is almost working now. Open the query in design
view. Next go to View, SQL View and copy and past it here.

 

[Guest]

Hey, sorry; this is sort of a mess. Let me know if you need anything else.
Thanks again.

SELECT tblHolds.Model, tblHolds.HoldDate, tblHolds.Line, tblHolds.Shift,
qryCurrentHoldQty.CurrentHoldQty, tblHolds.InitialQuantity,
tblHolds.Location, tblHolds.Remarks,
IIf(DateDiff('d',[HoldDate],Date())=1,DateDiff('d',[HoldDate],Date()) & "
Day",DateDiff('d',[HoldDate],Date()) & " Days") AS DaysOnHold
FROM qryCurrentHoldQty LEFT JOIN tblHolds ON qryCurrentHoldQty.HoldID =
tblHolds.HoldID
WHERE (((qryCurrentHoldQty.CurrentHoldQty)>0))
GROUP BY tblHolds.Model, tblHolds.HoldDate, tblHolds.Line, tblHolds.Shift,
qryCurrentHoldQty.CurrentHoldQty, tblHolds.InitialQuantity,
tblHolds.Location, tblHolds.Remarks,
IIf(DateDiff('d',[HoldDate],Date())=1,DateDiff('d',[HoldDate],Date()) & "
Day",DateDiff('d',[HoldDate],Date()) & " Days")
HAVING (((tblHolds.HoldDate) Between
NZ([forms]![Switchboard]![txtStartDate],0) And
NZ([forms]![Switchboard]![txtEndDate],Date())))
ORDER BY tblHolds.HoldDate;

We probably need the SQL for qrySELECTHoldsResults also.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here it is:

SELECT tblLines.Line, Sum(qrySelectHoldsResults.CurrentHoldQty) AS
SumOfCurrentHoldQty
FROM qrySelectHoldsResults RIGHT JOIN tblLines ON qrySelectHoldsResults.Line
= tblLines.Line
WHERE (((qrySelectHoldsResults.HoldDate) Between
NZ([forms]![Switchboard]![txtStartDate],0) And
NZ([forms]![Switchboard]![txtEndDate],Date())))
GROUP BY tblLines.Line;

Show us the SQL for what is almost working now. Open the query in design
view. Next go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hey everyone, I have a problem that hopefully can be fixed shortly.

I have a table called "Lines". It only contains one field, which is lines,
and it includes 5 lines: "Line 1", "Line 2", etc.
I also have a "Holds" query that will get all records for a field called
"Holds" for each line. I use the "Holds" query in my query which is having
problems. My query takes the holds from my "Holds" query and sums them up for
each line. However, it will only output a number for those lines that have
had any holds. I would like to show every line, even if it has zero holds. I
have tried to change the relationship to show all values from my "Lines"
table and any that match from my "Holds" query, but have been unsuccessful.
Any help would be greatly appreciated. Thank you.

 

[Guest]

Here is qryCurrentHoldQty too:

SELECT tblHolds.HoldID, ([InitialQuantity]-NZ(Sum([ReleaseQuantity]),0)) AS
CurrentHoldQty
FROM tblHolds LEFT JOIN tblReleases ON tblHolds.HoldID = tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity
ORDER BY ([InitialQuantity]-NZ(Sum([ReleaseQuantity]),0)) DESC;

We probably need the SQL for qrySELECTHoldsResults also.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here it is:

SELECT tblLines.Line, Sum(qrySelectHoldsResults.CurrentHoldQty) AS
SumOfCurrentHoldQty
FROM qrySelectHoldsResults RIGHT JOIN tblLines ON qrySelectHoldsResults.Line
= tblLines.Line
WHERE (((qrySelectHoldsResults.HoldDate) Between
NZ([forms]![Switchboard]![txtStartDate],0) And
NZ([forms]![Switchboard]![txtEndDate],Date())))
GROUP BY tblLines.Line;

Show us the SQL for what is almost working now. Open the query in design
view. Next go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hey everyone, I have a problem that hopefully can be fixed shortly.

I have a table called "Lines". It only contains one field, which is lines,
and it includes 5 lines: "Line 1", "Line 2", etc.
I also have a "Holds" query that will get all records for a field called
"Holds" for each line. I use the "Holds" query in my query which is having
problems. My query takes the holds from my "Holds" query and sums them up for
each line. However, it will only output a number for those lines that have
had any holds. I would like to show every line, even if it has zero holds. I
have tried to change the relationship to show all values from my "Lines"
table and any that match from my "Holds" query, but have been unsuccessful.
Any help would be greatly appreciated. Thank you.

 

[Guest]

Ouch! My brain just exploded. It looks like you have a query based on a query
based on a query based on a query and many of these queries are calling upon
the same tables. There's all kinds of traps and pitfalls in something like
this.

I suggest that you start from the beginning and see if there is a way to
simplify things or at least do things in fewer queries. It's also possible
that your data isn't normalized properly. Often convoluted queries and lots
of fancy code are indications that the tables just aren't set up right and
you really can't get the information that you want from the data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is qryCurrentHoldQty too:

SELECT tblHolds.HoldID, ([InitialQuantity]-NZ(Sum([ReleaseQuantity]),0)) AS
CurrentHoldQty
FROM tblHolds LEFT JOIN tblReleases ON tblHolds.HoldID = tblReleases.HoldID
GROUP BY tblHolds.HoldID, tblHolds.InitialQuantity
ORDER BY ([InitialQuantity]-NZ(Sum([ReleaseQuantity]),0)) DESC;

We probably need the SQL for qrySELECTHoldsResults also.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here it is:

SELECT tblLines.Line, Sum(qrySelectHoldsResults.CurrentHoldQty) AS
SumOfCurrentHoldQty
FROM qrySelectHoldsResults RIGHT JOIN tblLines ON qrySelectHoldsResults.Line
= tblLines.Line
WHERE (((qrySelectHoldsResults.HoldDate) Between
NZ([forms]![Switchboard]![txtStartDate],0) And
NZ([forms]![Switchboard]![txtEndDate],Date())))
GROUP BY tblLines.Line;


:

Show us the SQL for what is almost working now. Open the query in design
view. Next go to View, SQL View and copy and past it here.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Hey everyone, I have a problem that hopefully can be fixed shortly.

I have a table called "Lines". It only contains one field, which is lines,
and it includes 5 lines: "Line 1", "Line 2", etc.
I also have a "Holds" query that will get all records for a field called
"Holds" for each line. I use the "Holds" query in my query which is having
problems. My query takes the holds from my "Holds" query and sums them up for
each line. However, it will only output a number for those lines that have
had any holds. I would like to show every line, even if it has zero holds. I
have tried to change the relationship to show all values from my "Lines"
table and any that match from my "Holds" query, but have been unsuccessful.
Any help would be greatly appreciated. Thank you.