Is there a better practice ? (1 Viewer)

[Mihail]

Hello !

I have: (in my head)
A)
tblItemsQuantities
ID_ItemQuantity (PK)
ID_Item (FK)
Quantity (Long) 'Is OK. I need only integers
QuantitySign (Yes/No) ' Yes = Plus , No = Minus

B)
A form based on this table in order to input quantities.
The QuantitySign check box is hidden and I set it's default value in the Open event of the form based on what I need (to add quantities or to subtract quantities).

All this stuff is in order to make things simple for the users (very not experimented either in computers and in math). So, the users will input quantities only with PLUS sign.

So, is there a better practice ? Can I avoid the extra field QuantitySign ?

Thank you !

 

[Galaxiom]

An alternative technique is a fabricated ADO Recordset. These handy objects can include any extra fields you want such as a Yes/No to select the records. They are not connected directly to any tables. Existing only in RAM they are blazing fast to work with.

The table data is copied into the recordset which is then set as the Recordset Property of a bound form where the user interacts with any of the data.

Synchronising the data back to the underlying tables is all done with loops and database commands.

 

[Galaxiom]

BTW. Fabricated ADO recordsets are also fabulous as recordsets for listboxes and combos.

 

[Mihail]

Thank you for the answer Galaxiom.

I don't understand how to know at a later time if a quantity is negative or positive in order to calculate the stock.
Can you explain in more details ?

 

[jdraw]

Galaxiom,

Do you have a link or good sample code for reference? I'm not finding anything on Youtube
for Fabricated ado recordset, and haven't used them myself.

 

[Galaxiom]

I don't understand how to know at a later time if a quantity is negative or positive in order to calculate the stock.

Sorry. I didn't read the question closely enough. My answer was not relevant.

You do not need the QuantitySign field in the table. The field can store a signed integer.

Unbind the textbox. Write the value with the appropriate sign to the form's recordset in the AfterUpdate event of the textbox.

Rather than a hidden checkbox you could use the OpenArgs property of the form. Pass either 1 or -1 in the OpenForm command.

This would be the AfterUpdate.
Me.Recordset.Quantity = Me.Quantity * Me.OpenArgs

 

[Galaxiom]

Do you have a link or good sample code for reference? .

I have not located a comprehensive example.

Basically a fabricated (AKA shaped) ADO recordset has no ActiveConnection property. After creation, fields are appended to the Fields Collection with the Fields.Append Method.

Then the recordset is opened with a Dynamic Cursor and loaded with the data. This is most easily done with a loop through a conventional recordset based on the table or query to be used and the AddNew method.

Then set the fabricated recordset as the Recordset Property of a bound form. The recordset is updated with any form changes.

When the data needs to be transferred to the tables, the recordset is looped through and the data updated with database commands or via the conventional recordset.

I mainly use them to display massaged data from text files in forms but they are also good alternative to temporary tables.

If you don't need to add extra fields to the data then a Disconnected ADO recordset is another temporary table alternative. Unfortunately, although the recordset bound to the form is updated, these changes are not transferred back to the underlying data in the batch update after reconnection so it is still necessary to coordinate the updates to the back end manually.

 

[jdraw]

Thanks Galaxiom.

Here's a small simple example of code showing fabricated ADO data set

'Procedure:   testado
' Author    : Jack
' Date      : 04/12/2013
' Purpose   : Sample of fabricating an ADO data set -- no connection involved
'
' from Galaxiom's comments at
' http://www.access-programmers.co.uk/forums/showthread.php?t=257740#7
'------------------------------------------------------------------------------------
'
Sub testado()

          Dim objRs As ADODB.Recordset
10       On Error GoTo testado_Error

20        Set objRs = New ADODB.Recordset
          '
          'Create ado recordset with 3 fields
30        With objRs.Fields
40            .Append "PersonID", adChar, 11 
50            .Append "FullName", adVarChar, 50 
60            .Append "PhoneNumber", adVarChar, 20 
70        End With
          
80        With objRs
          
          'Open the recordset and add records
90            .Open
              
100           .AddNew
110           .Fields(0) = "123456789"
120           .Fields(1) = "General Purpose"
130           .Fields(2) = "(415) 235-5775"
140           .Update
              
150           .AddNew
160           .Fields(0) = "234556780"
170           .Fields(1) = "Private Partz"
180           .Fields(2) = "(615) 542-4442"
190           .Update
200           .MoveFirst

              'Print records to immediate window using field names or position
210           Debug.Print .Fields("PersonId"); .Fields("Fullname"); .Fields(2)
220           .MoveNext
230           Debug.Print .Fields(0); .Fields(1); .Fields(2)
240       End With

250      On Error GoTo 0
260      Exit Sub

testado_Error:

270       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testado of Module Module1"
End Sub

 

[Mihail]

And this... is not volatile ?

I know that is not an answer to my original question.
But, if I am in the right place at the right moment, maybe I can understand something from you, both.

 

[MarkK]

What I don't get is that a row in a table should describe a complete instance of a thing, and the fields are the dimensions of that thing. But what is described in a table called quantity? Quantity of what? And if your table doesn't answer, "quantity of what" then you have a table design problem. Don't you?

Quantity is a field in a table, not a row.

 

[Mihail]

Please, try to not lose me.
Place an @Mihail before your post if that post is addressed to me.
I think that your post (Mark) is addressed to me.

The ITEM is a generic name for something that can be measured using integers: like cars, bulbs, cups, trees, computers etc.
So, the quantity tell me "how many" cars, bulbs, cups etc I have.
Note please that ID_Item is a FK (Foreign Key or Family Key) on another table (tblItems)

...then you have a table design problem. Don't you?

I don't think so. I described the item's properties in tblItems.
I tried to simplify at maximum the table structure in my original post.
Of course that my real table contain (at least) one more field ID_Customer (FK on tblCustomers) but I am very sure that is not important here.

 

[MarkK]

@Mihail, I think a row should describe a multi-dimensional object, for instance, you could have a table for first names, and you could link to a first name from tblContact and from tblStudent and from tblEmployee, but you wouldn't, because a first name is a single dimension of a person, and there is a one-to-one relationship between a person and a first name. In that case the FirstName field should be a single dimension of the row that describes the person, not a linked row in a different table.

I think your quantity row violates this principle. If your quantity row had a type field, then maybe it would make sense, like your car might have a quantity of bulbs, but this is what I mean, that quantity is nothing by itself. Quantity, to have meaning, must be quantity of something, maybe even quantity on a certain date so you can measure that quantity changing in respect to a different quantity, time.

But what does it mean that one object, say a cup, has many related quantity records? Say the object "cup" has three related quantities 2, 5, and 8. What does that mean as a way to design data about cups? Does that mean there are 15 cups, and if so, why not have a quantity field in the cups record that says 15?

I think a row should describe a multi-dimensional object, and I would never link to a single dimension.

 

[jdraw]

@Mihail,
Can you give a realistic example of what you are thinking? It isn't clear to me either.

 

[Mihail]

@Mihail,
Can you give a realistic example of what you are thinking? It isn't clear to me either.

See the attachment, please.
YES. It is as simple as you see.
I'm wonder if the more experimented DBs designers have a better solution (see my original post).

Galaxiom's solution (post #6)

You do not need the QuantitySign field in the table. The field can store a signed integer.

Unbind the textbox. Write the value with the appropriate sign to the form's recordset in the AfterUpdate event of the textbox.

Rather than a hidden checkbox you could use the OpenArgs property of the form. Pass either 1 or -1 in the OpenForm command.

This would be the AfterUpdate.
Me.Recordset.Quantity = Me.Quantity * Me.OpenArgs

is an working one, but I don't think that is a better one because I need to open the same recordset (for a certain ITEM) many times in order to ADD / SUBTRACT quantities values.
So I'll need a lot of extra code in order to show, again, each quantity with the "+" sign. (or is there an easy workaround ????)

 

[jdraw]

@Mihail,

I must be missing something you see as obvious. To me it seems you have a small component of a real situation and have set up some tables, almost as a proof of some concept.
Typically, I see QuantityOfItems as some sort of calculation after some process
eg Sales, Purchases..
I see Order, Customer and/or Supplier and quantity Sold, quantity Purchased as part of the bigger picture.
I can see adding Items(to Inventory) when you make purchases from your Supplier, and subtracting Items (from Inventory) when making Sales to Customers.
I don't see the need for tblItemsQuantities.
So I guess I'm missing your point.

 

[Mihail]

Please, try to see beyond of my field names.

Think about the room in your house where you store some products in order to eat this products. At the beginning this room is empty.
Then you start to buy:
Milk , +3
Bread , +5
Apples , +24

But you must eat:
Milk , -1
Bread , -2
Apples , -6
Milk , -1
Bread , -1
Apples , -11

Oh... I have only 1 milk so, I need to buy more. Lets go to the story =>
Milk , +4
Apples , +20
Bacon , +4

Now, you design a DB in order to see, at every moment, the stock.
But you like that your 8 years boy to be able to fill this DB.
He know to read, so he know to select "Milk", "Bread" etc, he know the numbers so he know that you buy 12 apples and/or he eat 4 apples.
But, he don't know that when you buy 12 apples this quantity should be added to the DB with the "+" sign (Apples , 12) and, if he eat 3 apples, this quantity should be added to the DB with "-" sign (Apples , -3).

So: How you, as very skilled DBs designer, manage the things in order that your very young boy to be able to fill the ItemsQuantities table ?

Sorry, but is impossible for my English to describe my real situation.
But the example with your little boy is a very real description. It is what I need. A solution in order that the users to input this quantities using ONLY the "+" sign.
In other words, without the need to press any key for sign.

 

[MarkK]

Your system uses time implicitly. You make the assumption that all your quantity adjustments occur in the past, so that is like a hidden field in your tables. See how that is a dimension that exists in your calculation?

We always record data as it varies in respect to some other data, so we might record how weight varies in respect to shoe size, or how first name varies in respect to last name, or how quantity varies in respect to time, but we never record just a single dimension.

Your pattern is that of a sales order, where the time of the quantity adjustment belongs to the parent object, so if you go to the store and do this . . .

Milk , +4
Apples , +20
Bacon , +4

. . . that occurs in time. In a database, that would be an Order, and OrderDetails . . .

tOrder
OrderID
CustomerID
OrderDate

tOrderDetail
'and this is your quantity table
OrderDetailID
OrderID 'and here is the time dimension for this data, inherited from the parent record
Quantity
Product

. . . and if you didn't have the Order table, you would need to put the date in the child record, and that is what is missing from your quantity table, when it happened.

 

[Mihail]

I tried my best in order to obtain an answer to my original question.

Except Galaxiom (post #6), all the others try to tweak my DB.
Thank you, all of you, but is no need to do this. For now I'm very happy with my tables structures, with my relationships and I have a normalized DB that answer very well to user's needs.

For now I have a single question (post #1 and the DB in post #14)

I don't agree Galaxiom's solution for some reasons (post #14). But this was the single real answer to my question.