Query Based On Table Matching Another Query

[Bones_11]

Hello all,

Been working on this for a while now and can't get it to work how I'd like after trying a few things.

I have a form ("Production Form") from where users input start and end times for various processes against a particular product. Currently, I have (or had) a query (and linked report) "ProductionDurations" where durations for each of the processes were calculated for different product runs.

I have since tried to adapt this query to include reference values contained in another query ("ProductionDurationsPerBulkLitre", based on a "Products" table) for how long each process should take for each production, by multiplying by the volume processed. Here's what I have at the moment in the "ProductionDurations" query:

SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration, DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration, DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration, DateDiff("n",[TankCIPStartTime],[TankCIPEndTime]) AS TankCIPDuration, [Production Form].[ProductionRef#], [Production Form].ProductionDate, [Production Form].Product, [BlendlineCIPDuration]+[FlavourMixDuration]+[BlendlineDuration]+[TankCIPDuration] AS TotalDuration, ProdWeek([Production Form].[ProductionDate]) AS ProductionWeek, [Production Form].BulkVolumeLitres, [Production Form].[BulkVolumeLitres]*[ProductDurationsPerBulkLitre].[BlendlineCIPTimeMinsPerBulkLitres] AS ExpectedBlendlineCIPDuration
FROM [Production Form], ProductDurationsPerBulkLitre
ORDER BY [Production Form].[ProductionRef#];

Rather than returning what I require, the above bolded part is returning a calculated value for each production against each of the products. What I require is a calculated value for each production against the product that has been selected on the form.

I'm aware I may not be making this as clear as possible, and that the syntax of my SQL may be poorly-constructed, so feel free to ask me further questions.

Any help will be gratefully appreciated at this stage!

 

[namliam]

This is because you have what is called a Cartesian product... you didnt join the two tables involved in the query:

FROM [Production Form], ProductDurationsPerBulkLitre

 

[CJ_London]

You can't link a form to a table in the way you are trying to do it. Since you have not used table identifiers in your code (e.g. which table does [BlendlineCIPStartTime] belong to?) it is only possible to suggest what it should look like.

At the moment you are generating what is called a cartesian query, where all records are displayed from both recordsets, so if table1 has 100 records and table2 15 records you will get 1500 records returned.

If [Production Form is actually a table then you need to create an inner join between the two tables - something like

FROM [Production Form] INNER JOIN ProductDurationsPerBulkLitre ON [Production Form].ProductID=ProductDurationsPerBulkLitre.ProductID

However if [Prodcution Form] is actually a form then you need to remove [Production Form] from this

FROM [Production Form], ProductDurationsPerBulkLitre

And as an example replace this

ProdWeek([Production Form].[ProductionDate])

with

ProdWeek(Form![Production Form]![ProductionDate])

 

[Bones_11]

Thanks for the responses guys.

To answer your question CJ, the Production Form is based on a table with the same name.

I had done some research through these forums and Google, and had a thought that it might be an INNER JOIN but couldn't figure it out. I've tried to adapt the code based on your advice, and now have:

SELECT DateDiff("n",[BlendlineCIPStartTime],[BlendlineCIPEndTime]) AS BlendlineCIPDuration, DateDiff("n",[FlavourMixStartTime],[FlavourMixEndTime]) AS FlavourMixDuration, DateDiff("n",[BlendlineStartTime],[BlendlineEndTime]) AS BlendlineDuration, DateDiff("n",[TankCIPStartTime],[TankCIPEndTime]) AS TankCIPDuration, [Production Form].[ProductionRef#], [Production Form].ProductionDate, [Production Form].Product, [BlendlineCIPDuration]+[FlavourMixDuration]+[BlendlineDuration]+[TankCIPDuration] AS TotalDuration, ProdWeek([Production Form].[ProductionDate]) AS ProductionWeek, [Production Form].BulkVolumeLitres, [Production Form].[BulkVolumeLitres]*[ProductDurationsPerBulkLitre].[BlendlineCIPTimeMinsPerBulkLitres] AS ExpectedBlendlineCIPDuration FROM [Production Form] INNER JOIN ProductDurationsPerBulkLitre ON [Production Form].Product=ProductDurationsPerBulkLitre.Product
ORDER BY [Production Form].[ProductionRef#];

... although this is returning a "Type mismatch in expression" error. When tinkering, I'm getting errors telling me there is an error in my FROM clause. I'm guessing my syntax is completely out here - any thoughts on how to resolve?

Cheers!

 

[namliam]

You shouldnt have a form with the exact same name as a table. To make this a little easier it is "good practice" to prefix all your objects
tbl
qry
frm
mcr
mdl
for the respective objects

Jsut to keep things simple at the start, try this:

SELECT *
FROM [Production Form] 
INNER JOIN ProductDurationsPerBulkLitre ON [Production Form].Product=ProductDurationsPerBulkLitre.Product

in a new query and see if that works, NOTE that the Product in BOTH tables must be of the same type.

Also I have a gutt feeling about a host of design issues in your database, not to mention the amount of good practice things.

 

[CJ_London]

Your error is coming from your join

[Production Form].Product=ProductDurationsPerBulkLitre.Product

One of these is probably numeric and the other text

I would hazard a guess that you are using a lookup for product in your production form table design which frequently causes this sort of issue.

 

[Bones_11]

You shouldnt have a form with the exact same name as a table. To make this a little easier it is "good practice" to prefix all your objects
tbl
qry
frm
mcr
mdl
for the respective objects

Jsut to keep things simple at the start, try this:

SELECT *
FROM [Production Form] 
INNER JOIN ProductDurationsPerBulkLitre ON [Production Form].Product=ProductDurationsPerBulkLitre.Product

in a new query and see if that works, NOTE that the Product in BOTH tables must be of the same type.

Also I have a gutt feeling about a host of design issues in your database, not to mention the amount of good practice things.

Sorry for the delayed response on this.

Thanks for the pointer, although will changing the names now affect the form I've built?

I tried the code that you posted in a brand new query, and am receiving a type mismatch error. The "Product field" is a lookup of the corresponding field in the "Products" table - is there a simpler alternative to this which will allow users to select from a drop-down in the form?

I suspect you are correct regarding design issues, I appear to have forgotten even the basics from high school! Would attaching a sample database help?

 

[CJ_London]

I tried the code that you posted in a brand new query, and am receiving a type mismatch error

I refer you to post #6

 

[Bones_11]

I refer you to post #6

Thanks, I've now realised what you were trying to tell me. Having made both fields the same type, the query posted by namliam (adapted to the new field names) now "works", but produces no results against all the fields from "tbl_BlendlineProductionForm" and "qry_ProductDurationsPerBulkLitre".

Here's the code I've entered:

SELECT *
FROM [tbl_BlendlineProductionForm]
INNER JOIN qry_ProductDurationsPerBulkLitre ON [tbl_BlendlineProductionForm].Product=qry_ProductDurationsPerBulkLitre.Product

 

[namliam]

Likely because your Product column isnt exactly the same in value between the two (table and query)

 

[Bones_11]

Likely because your Product column isnt exactly the same in value between the two (table and query)

Excuse my ignorance, but I don't fully understand what you mean.

In the table ("tbl_BlendlineProductionForm"), "Product" is a text field based on the list of "Product" in "tbl_Products", which is also a text field. In the query ("qry_ProductDurationsPerBulkLitre") similarly, "Product" is pulled from "tbl_Products". Both fields match up in terms of selection choices and as far as I can see otherwise. Am I missing something obvious?

 

[namliam]

it is highly unusual to have a join on text fields, particular since the product also is in the product table it should have a PK to it and you should be better off storing said PK to the ProductionForm table.

Without seeing your actual database with the actual data, I cant really see what is actually wrong... Would you be using a "lookup" column by chance?
Does your qry_ProductDurationsPerBulkLitre contain the data you expect?

 

[Bones_11]

it is highly unusual to have a join on text fields, particular since the product also is in the product table it should have a PK to it and you should be better off storing said PK to the ProductionForm table.

Without seeing your actual database with the actual data, I cant really see what is actually wrong... Would you be using a "lookup" column by chance?
Does your qry_ProductDurationsPerBulkLitre contain the data you expect?

Yes, it's a lookup field in the "Product" field in "tbl_BlendlineProductionForm", joined on an autonumber primary key "ProductID" field from "tbl_Products". Is this a no-no?

Would uploading a copy of the database help in understanding where I'm going wrong?

"qry_ProductDurationsPerBulkLitre" is returning the data I want, which is essentially just calculations from "tbl_Products" based on expected durations.

My aim overall is to have, in a new query ("qry_BlendlineProductionDurations"), how long it should take per bulk litre for a given product (from "tbl_Products" via "qry_ProductDurationsPerBulkLitre") along with how long it has taken against a given bulk litre value (from "tbl_BlendlineProductionForm").

 

[namliam]

Is this a no-no?

Yes Lookups on table level are no-no... because of this particular issue, among other concerns.

To make sure, yes if you can upload a version of your database with the tables and queries concerned... perhaps with some of the data altered in case of privacy concerns...

 

[Bones_11]

Yes Lookups on table level are no-no... because of this particular issue, among other concerns.

To make sure, yes if you can upload a version of your database with the tables and queries concerned... perhaps with some of the data altered in case of privacy concerns...

I was unaware of this. Not sure how else I'd be able to pull through the required data otherwise though.


Generic database with any sensitive info removed is attached. Thanks for your help Mailman.

 

[namliam]

Yeah as I expected, I suggest you test this yourself....
Go into the design of your tbl_BlenlineProductionForm and change the lookup back to a display control "Text Box", you will see the actual values beeing stored in the table is actually the PK of your product table, NOT the actual text that you expect it to be due to the lookup value. It is actually creating more havoc by chaning your Autonumber (integer) into a text field, making it unable to (re)join in a query.

You can "fix" your query by
1*) Remove the lookup from your table (and remove all lookups from tables if you have others) Tables are data, you want to see your root data not some modification of the data. "Beautified" data, which you do with the lookup, belong on user frontends like in forms, reports and exports, NOT in tables.
2) Changing the datatype of your "Product" column to Number
3) adding the ProductID into your qry_ProductDurationsPerBulkLitre. Then using that to join the two on the actual ProductID that is stored in the "product" column of your tblBlendlineProductionForm.

*1) Offcourse is optional, but the lookup actually stores the bound column as you have seen if you took my advice and can create all kinds of mind games inside your database.

 

[Bones_11]

Yeah as I expected, I suggest you test this yourself....
Go into the design of your tbl_BlenlineProductionForm and change the lookup back to a display control "Text Box", you will see the actual values beeing stored in the table is actually the PK of your product table, NOT the actual text that you expect it to be due to the lookup value. It is actually creating more havoc by chaning your Autonumber (integer) into a text field, making it unable to (re)join in a query.

You can "fix" your query by
1*) Remove the lookup from your table (and remove all lookups from tables if you have others) Tables are data, you want to see your root data not some modification of the data. "Beautified" data, which you do with the lookup, belong on user frontends like in forms, reports and exports, NOT in tables.
2) Changing the datatype of your "Product" column to Number
3) adding the ProductID into your qry_ProductDurationsPerBulkLitre. Then using that to join the two on the actual ProductID that is stored in the "product" column of your tblBlendlineProductionForm.

*1) Offcourse is optional, but the lookup actually stores the bound column as you have seen if you took my advice and can create all kinds of mind games inside your database.

I now have the query doing exactly what I wanted it to, thank you so much for helping with this and with the general beginners' stuff as well. Much appreciated friend.

 

[namliam]

Your welcome, now ever again use Lookups in tables