mabino79201 (1 Viewer)

[mabino79]

adding two query in one but getting duplicate records.

SELECT [qryReserveFolio Query].Product, [qryReserveFolio Query].Qty, qrySalesFolio.Qty
FROM [qryReserveFolio Query], qrySalesFolio;

can anyone help, as its showing duplicate values/ rows

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[arnelgp]

you create Join:

SELECT [qryReserveFolio Query].Product, [qryReserveFolio Query].Qty, qrySalesFolio.Qty
FROM [qryReserveFolio Query] Inner Join qrySalesFolio On [ [qryReserveFolio Query].Product = qrySalesFolio.Product

 

[NauticalGent]

Welcome to AWF!

 

[theDBguy]

Hi. Welcome to AWF!

 

[pbaldy]

Welcome aboard. I moved your thread to a more appropriate forum.

 

[plog]

SELECT [qryReserveFolio Query].Product, [qryReserveFolio Query].Qty, qrySalesFolio.Qty
FROM [qryReserveFolio Query], qrySalesFolio;

My guess is that you just need 1 correctly constructed query. Can you post the SQL of qryReserveFolio and qrySalesFolio?

 

[mabino79]

SELECT [qryReserveFolio Query].Product, [qryReserveFolio Query].Qty, qrySalesFolio.Qty FROM [qryReserveFolio Query], qrySalesFolio;

getting repeated rows duplicate

 

[mabino79]

View attachment 96265

getting repeated rows duplicate

screenshot relationship, inform if anything required to change

 

[plog]

You didn't post the SQL I requested, but that Relationship Tool told me what I needed. You are essentially storing a piece of data in the name of your tables that should go into a field.

tblSalesFolio and tblReserveFolio should not be seperate tables, nor should tblPOFolio. The data from all three of those tables need to go into just 1 table--let's call it tblFolio--that has all the fields those tables have plus 1 more [FolioType]. In that field you will store if the record is for 'PO', 'Sales' or 'Reserve'.

Also, I bet you can combine tblPurchase, tblSales and tblReserve into 1 table as well, there's considerable overlap among their fields. Do that and there's no need for 2 seperate queries to being with you can just go straight to the final query.

 

[mabino79]

You didn't post the SQL I requested, but that Relationship Tool told me what I needed. You are essentially storing a piece of data in the name of your tables that should go into a field.

tblSalesFolio and tblReserveFolio should not be seperate tables, nor should tblPOFolio. The data from all three of those tables need to go into just 1 table--let's call it tblFolio--that has all the fields those tables have plus 1 more [FolioType]. In that field you will store if the record is for 'PO', 'Sales' or 'Reserve'.

Also, I bet you can combine tblPurchase, tblSales and tblReserve into 1 table as well, there's considerable overlap among their fields. Do that and there's no need for 2 seperate queries to being with you can just go straight to the final query.

Ok thanks for your suggestion, will do and update

 

[mabino79]

SELECT tblFolio.TypeofFolio, tblProducts.ProductCode, Sum(tblFolio.Qty) AS SumOfQty
FROM tblProducts INNER JOIN tblFolio ON tblProducts.ProductsID = tblFolio.Product
GROUP BY tblFolio.TypeofFolio, tblProducts.ProductCode;

getting the total nos. of, but can you tell me how to make the report for Product base Sale, Purchase & Reserved Qty separately

 

[mabino79]

SELECT tblFolio.TypeofFolio, tblProducts.ProductCode, Sum(tblFolio.Qty) AS SumOfQty
FROM tblProducts INNER JOIN tblFolio ON tblProducts.ProductsID = tblFolio.Product
GROUP BY tblFolio.TypeofFolio, tblProducts.ProductCode;

getting the total nos. of, but can you tell me how to make the report for Product base Sale, Purchase & Reserved Qty separately

View attachment 96268

TRANSFORM Sum(qryProductPurchase.SumOfQty) AS SumOfSumOfQty
SELECT qryProductPurchase.Product, Sum(qryProductPurchase.SumOfQty) AS [Total Of SumOfQty]
FROM qryProductPurchase
GROUP BY qryProductPurchase.Product
PIVOT qryProductPurchase.TypeofFolio;


created CrossTabl Query,

if the value is Null than how to make zero so it can be calculated

 

[mabino79]

TRANSFORM Sum(qryProductPurchase.SumOfQty) AS SumOfSumOfQty
SELECT qryProductPurchase.Product, Sum(qryProductPurchase.SumOfQty) AS [Total Of SumOfQty]
FROM qryProductPurchase
GROUP BY qryProductPurchase.Product
PIVOT qryProductPurchase.TypeofFolio;


created CrossTabl Query,

if the value is Null than how to make zero so it can be calculated

View attachment 96269

 

[arnelgp]

TRANSFORM Nz(Sum(qryProductPurchase.SumOfQty), 0) AS SumOfSumOfQty
...
...

 

[The_Doc_Man]

It looks like you have actually fixed the duplicates problem. I want to highlight the cause for that condition so that others reading the post (and most important, you) will be able to recognize and avoid the error in the future. You posted this query. (I didn't include code markers because I want to use color highlights instead.)

SELECT [qryReserveFolio Query].Product, [qryReserveFolio Query].Qty, qrySalesFolio.Qty FROM [qryReserveFolio Query], qrySalesFolio;

The problem is highlighted in red. This query is perfectly legal in SQL, but you have to recognize what you are really requesting. You see, SQL is based on set theory. It will give you every possible combination of records from those two tables. In this query, you have what is called a Cartesian JOIN, and you may also see the name Permutation JOIN.

You got apparent duplication of some data because you did not constrain the selection and/or presentation of content from the two tables. You were advised to use JOIN ... ON syntax, which constrains SQL to only show you records that have some particular value in common with a field value in the other table. You will often see this concept - field from table A matching field from table B - described via a Relation. Technically, SQL still starts out preparing to give you all of the possible combinations - but it then immediately filters out the records for which the joined fields do not match, and that eliminates a lot of the "clutter" that usually comes back after a Cartesian JOIN. That will eliminate the apparent repetition.

 

[mabino79]

on a crosstab query based report can we make a filter if the value is Zero or Null, row will be hide or removed.

 

[mabino79]

TRANSFORM Nz(Sum(qryProductPurchase.SumOfQty), 0) AS SumOfSumOfQty
...
...

in a crosstab query based report can we make a filter

if the value is Zero or Null, that row will be hide or removed from the report

 

[arnelgp]

on the report, you can Change the Recordsource on the Open event.
since this is a crosstab you will need a function that will inspect each row
if the total is null or zero.
you call this function on your New Query (Query builder on Report) to include/exclude
those records.

see this demo.
when you open Query1, you'll see a blank record at the beginning.
this record is excluded from the sampleReport report.

 

[mabino79]

Required Sub Form Row Selected Product --> Total of Product Qty Purchased - Sales

in Main Form from Sub Form