Using current length (1 Viewer)

[MartynE]

The query I'm trying to build is to keep track of the length of a stave.
Let me explain the query:
- Bought length is applied only 1 time at the start of the record.
- First time I have to use the Bought length to calculate what is left
- Saw off length and sawing width gives the total piece sawed off for that order.
- That gives a new current length left of the stave which has to be used for the next order until the stave is at such length it isn't useable anymore.

The problem I bump into is that I can't seem to find a way to get the totals and to use the new current length for the next order.

Hope someone can help, I'm new to writing queries so any help is appreciated.

 

[CJ_London]

you will need a formula which will be something like this in a query builder:

CurrentLength: boughtlength-dsum("[sawingwidth]","StaveTbl","StaveRef = " & StaveRef)

You'll need to substitute with your own field and table names. It is assumed StaveRef is a number

Not sure of accuracy required, but do you need to allow for the sawcut as well? if so then you can use "[Sawingwidth]+5" in the dsum function where 5 is the width of your saw (in mm)

 

[MartynE]

Thanks! Tried but gives me even more headache, what I'm trying to do can be much simpler.
I make new columns for
- [sawed off] per order which is [saw width]+[saw length]
- [total sawed off] which has to be the sum of all [sawed off]
- current length would be [bought length] - [total sawed off]

Sounds so easy, yet I get errors, errors and more errors since I have no experience in building queries.

Maybe I was a bit vague about using the current length, it doesn't really have to use that in the calculation but it has to be shown right on the form. So it don't really matter if it's calculated after the order is placed in the database.

 

[jdraw]

It might help if we knew more about your tables and relationships.Could you post a jpg of these?
In concept it seems you have

Inventory (stave in some unit of measure) on some date.
Available inventory = Inventory(Date Last inventory review) - Sum(Inventory used {since Date Last Inventory review})

I think this is much like CJ is suggesting. I'm looking at this as Inventory -- you buy some Inventory, you do stock taking (actual count of real items) from time to time, you use material from Inventory

At any time your current inventory would be along this

Last Inventory Count on Date - Sum (Usage from Inventory by Date since Last Inventory Count Date)

 

[MartynE]

This is gonna be a new part of our company so there are just 2 tables for this matter.
VR with columns
- VRID
- Aankoop_lengte
- Aankoop_datum
- and others which are not important

VRMUTSTAF with:
- AF_lengte
- AF_zaagbreedte
- AF_datum
- AF_ordnr
- VRID

Bound together with VRID one(VR)-to-many(VRMUTSTAF)

About the inventory thing, It's like that indeed except that you can't restock the current stave. By other words, there is a +5 meters at start from there you can only saw off pieces. If you can get them back on you are my nominee for a nobelprize . So for a new stave of the same kind will be a new record in the table.

 

[CJ_London]

Does this query give you the result you are looking for?

SELECT VR.Annkoop_datum, VR.Annkoop_lengte, nz(dsum("AF_lengte","VRMUTSTAF","VRID = " & VRID),0) As Used
From VR

 

[CJ_London]

If you can get them back on you are my nominee for a nobelprize

There is always finger jointing

 

[MartynE]

There is always finger jointing

No nominee for you, but good try. We are in industrial gearboxes

I will give this a try first thing in the morning, thanks !

 

[MartynE]

It's very close, it sums up the length perfectly. Yet this is not what is used. What is used is AF_lengte + AF_zaagbreedte. i'll try to play around a bit to see if I get it working with both

GOT IT just a + [fieldname]

SELECT VR.Annkoop_datum, VR.Aankoop_lengte, nz(dsum("AF_lengte+AF_zaagbreedte","VRMUTSTAF","VRID = " & VRID),0) As Used From VR
Thanks for your help!

 

[CJ_London]

Glad to help!

 

[MartynE]

Those queries give me so much headache :banghead:

My program cracked down, so everything I did past 2 days has to be redone.

Now this query won't let me add records, which in the previous version was possible and I don't see what I'm doing different this time.

I'll post the SQL maybe you see something?

SELECT VR.Materiaaltype, VR.Binnendiameter_mm, VR.Buitendiameter_mm, VR.CERTVLG, VR.MATO, VR.Trekvastheid, VR.Rekgrens_P02, VR.Rek, VR.Insnoering, VR.Kerfslagwaarde, VR.VRID, VR.Aankoop_lengte, nz(DSum("AF_lengte+AF_zaagbreedte","VRMUTSTAF","VRID = " & [VR].[VRID]),0) AS Used, [Aankoop_lengte]-[Used] AS [Huidige lengte], VRMUTSTAF.AF_lengte, VRMUTSTAF.AF_zaagbreedte, VRMUTSTAF.AF_datum, VRMUTSTAF.AF_ordnr
FROM VR INNER JOIN VRMUTSTAF ON VR.VRID = VRMUTSTAF.VRID
GROUP BY VR.Materiaaltype, VR.Binnendiameter_mm, VR.Buitendiameter_mm, VR.CERTVLG, VR.MATO, VR.Trekvastheid, VR.Rekgrens_P02, VR.Rek, VR.Insnoering, VR.Kerfslagwaarde, VR.VRID, VR.Aankoop_lengte, VRMUTSTAF.AF_lengte, VRMUTSTAF.AF_zaagbreedte, VRMUTSTAF.AF_datum, VRMUTSTAF.AF_ordnr;

Edit: When I open the query I can see the line for a new record, it just doesn't let me type in it.

Edit2: I can only type in columns from table [VR] and I can't in columns from [VRMUTSTAF] does it has anything to do with a one-to-many relationship. I tried deleting it but that didn't work.

 

[CJ_London]

There are a couple of things:

It could be because you are using GROUP BY, which does not produce an updateable query (so not sure why you are seeing the 'new' line)

Also you now have a calculated value (Used) which can also make the query not updateable

A couple of things the try:

1. Change SELECT to SELECT DISTINCT and remove the GROUP BY
2. Change the query recordset type property from Dynaset to Dynaset (Inconsistent Updates) - note if the query is a recordsource to a form you do this in the form data properties

 

[MartynE]

You know what the funny thing is..

This query is the exact same as it was in the application that crashed, and there everything was updateable.

New SQL

SELECT DISTINCT VRMUTSTAF.AF_lengte, VRMUTSTAF.AF_zaagbreedte, VRMUTSTAF.AF_datum, VRMUTSTAF.AF_ordnr, nz(DSum("AF_lengte+AF_zaagbreedte","VRMUTSTAF","VRID = " & [VR].[VRID]),0) AS Used, [Aankoop_lengte]-[Used] AS [Huidige lengte], VR.VRID, VR.Aankoop_lengte, VRMUTSTAF.VRMUTID
FROM VR INNER JOIN VRMUTSTAF ON VR.VRID = VRMUTSTAF.VRID;

Sometimes an error of missing operator "VRID =" comes out .. This is after I change some settings etc

 

[CJ_London]

OK - so is your problem resolved?

 

[MartynE]

No still stuck, the question remains. I know I also had this problem yesterday. But no idea what I did to make it work. Probably a lot of luck helped me

When I open a new query with just columns from VRMUTSTAF I can edit all. The moment I add VR to the party I can't update anymore..

 

[CJ_London]

changing your recordset type as previously suggested should solve this problem.

If you have been rebuilding, check your relationships between the two tables

 

[MartynE]

Query is working, yet the subform is shown blank. Maybe the conclusion is that today just isn't my day

Edit: With blank I mean there are no records shown on the form. In the query are all the records which is good.

SQL of the "working" one (query wise):

SELECT VRMUTSTAF.AF_lengte, VRMUTSTAF.AF_zaagbreedte, VRMUTSTAF.AF_datum, VRMUTSTAF.AF_ordnr, VR.Aankoop_lengte, VRMUTSTAF.VRID, VR.VRID, VRMUTSTAF.VRMUTID, nz(DSum("AF_lengte+AF_zaagbreedte","VRMUTSTAF","VRID = " & [VR].[VRID]),0) AS Used, [Aankoop_lengte]-[Used] AS [Huidige lengte]
FROM VR INNER JOIN VRMUTSTAF ON VR.VRID = VRMUTSTAF.VRID;

 

[MartynE]

Got it! It was the property Data entry of the form that was set to yes..
Last error I hope is:
syntax error (missing operator) in query expression 'VRID = '

I will google this and hope I can find it and post it here

This is the one giving the troubles.

Used: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VRID = " & [VR].[VRID]);0)

 

[MartynE]

Okay I lost all hope now and am desperate on what is causing this. Especially when I think off that it all worked before the program decided to crash

Some more info on the error: The subform shows the right records and it is possible to update the existing records. When you want to add a new records in this subform it gives the syntax error (missing operator) in query expression 'VRID = '

In the query you can add new records with no problem at all.

Edit: From what I see now I can tell it is trying to calculate [used] and [huidige lengte] while there is no data for it yet so there is no autonumber ID given to the record on the moment it tries to get that value. Which gives the syntax error.

One extra note: The syntax error is on VRID which is not supposed to be a autonumber field. The only autonumber field is [VRMUTID] which is the mutation ID. But when I get rid the VRID autonumber field the query doesn't work anymore.

 

[CJ_London]

You've been working hard!

the problem is your Nz - you should use a comma rather than a semi colon as corrected here

nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID])[COLOR=red],[/COLOR]0)