Manipulere data i access 2007

"Mit spørgsmål er: Kan jeg lave en formular ..."

Answer is yes, but I think you need to be a bit more specific in what you need help with.

First of all your tables/fields and table relationships need to be in order, and if they are then I'd suggest you go ahead and try, and if you need help then place a question.

Hej Terry

Tak for svaret.

Jeg prøver at beskrive det:

Jeg har en tabel kaldet Tuborg

Den indeholder varer, som består af følgende felter:

ID, Type (sodavand, øl, etc), navn, strørrelse, beholdning, startbeholdning

Jeg ønsker en måde, at kunne maipulere beholdningen, således at jeg ved vareudlevering, kan kalde en formular og her vælge (gerne i en dropdown) først type, så navn+størrelse og til sidst antal. Formularen skal så ved afslutning (tryk en tast/knap) opdatere databasen Tuborg med de nu mindre beholdninger.

Forstår du eller er jeg upræcis? :)

Hej Brian?
OK, so you have only one table with various products.

What I would suggest is that you also have a table (order) or maybe even tables (order lines) which you use to store information on what is going out of your "lager".

So you enter that information about the order and the products in the order. Then when you press a button you will need some code (VBA) which runs through the order lines (products) and deducts them from your lager.

Hope you understand that

Hej Terry

Ja, det er Brian. :)

Ok, så du tænker at jeg opretter en Ordre-tabel, hvor jeg beder om opslag i Lager tabel (Type, Navn, Str) of tilføjer antal_udleveret. Måske også et felt til hvem, der modtager denne ordre.

Du nævner ordre_linjer i stedet - det er lidt mere specifikt, men måske meget godt, til optælling bagefter.

Det vil dog betyde, at min form skal danne en post for hver ordrelinje?

Jeg prøver at forstå det bagved liggende - og hvis jeg ikke er helt tabt endnu, så er det delt op, så jeg danner en lager tabel med oprindeligt lager, en ordre_linje tabel med de ordrer der kommer ind - og slutteligt en beholdning, baseret på lager minus ordre? Er det nogenlunde korrekt forstået?

Having order/order lines tables is just an idea, you may not have the need for these, but you do need some way of storing information of the products you want to deduct from your lager, even if its just temporary.

Normally you would have one or more tables which contain "lager transactions"  products going in and products going out. Your order/order line tables would be those going out, and you could also have a table of products you receive to add to you lager.

You have your products table (Tuborg) in this case which contains all of the various products you have, and it also contains the  number (beholdning) and also a , startbeholdning if you need that.

Then when you have products going in or out they are in separate tables which get deducted/added to Tuborg when you accept the transaction by pressing a button.

Ideen er jo god... altså at kunne styre ind/ud.

Jeg vil jo sikkert modtage flere varer i løbet af dagene, festivalen løber.

Men jeg kan ikke helt komme fra ideen til databasen. Det skal jo bare være simpelt.

Hovedmenu med 5 knapper:

Se varebeholdning (lavet via en rapport)
Indtast varer (Formular - bruges inden festival starter til at populere databasen)
Print varebeholdning (printer den nuværende lagerbeholdning)
Udlever varer (formular, som opretter en ordre og trækker ordrens indhold ud af varelageret)
Indsæt varer (formular, som bruges til at tilføje flere varer under festivalen)

Det burde være simpelt nok at lave ikke? :D

Dit sidste svar er lige præcis det, jeg er efter.

(Vil du gerne have svaret på engelsk egentlig?)

"Det burde være simpelt nok at lave ikke? :D "

Yes it is as long as you know how.

It sounds as though its not going to be used for more than a few days right, so I'm wondering if its at all necessary, do you need to do it in Access?

Without knowing your full requirement its not easy to suggest what the best solution is. The suggestion of having ind/ud may be overkill?

Well we used to have an excel sheet, but I thought it would be great to use access instead, as there are some fast and easy ways to show the inventory and print reports etc.

The Excel sheet got large and wasnt that well made, so everything became manually written in it. I would like the system to do the math (maybe eliminate some errors from the math-not-so-savvy users).

And yes - its 5-6 days a year, but I really would love for us to have a nice interface to work with.

Sorry just seen this (Vil du gerne have svaret på engelsk egentlig?)

No need, after 30+ years in DK I'm comfortable with Danish, it should maybe be me asking you if you want me to go over to Danish ;-)

Begge dele fungerer fint.

Jeg vil som sagt bare gerne prøve at have det kørende i access. Brugerfladen er lettere for brugerne (de er fra 40 til 65 år) og det kan udvides til at omfatte alt, hvad jeg skal bruge af statistikker efterfølgende.

The idea I've suggested I thin would be a good start and also quite simple to make, and if its going to be used in the future too then certainly worth spend the time on making it.

The is an example database (template) you should be able to create which might give you further ideas.
You can find it if you open Access (not an existing Access dB). You should be able to see different templates you can use to create a new database. Its called "Product Inventory"

You could take a look and see what you think. Probably a bit over kill but might give you some ideas for the future...

Is it going to be used by a number of people at the same time? On more than one PC in a network?

Nein! :)

It will be running on one computer in our depot.

One order at a time also.

OK, that makes things easier

Yes, der er intet kompliceret i det tænker jeg. Jeg er bare for dum og træt til lige at fatte det.

:-)
What is it you dont understand?

You already have table/form for your products (Tuborg)

So now I would suggest that you create a table for orders and another for order lines.
Here's a simple video which might help https://www.youtube.com/watch?v=E_nMGZYlvSM

I'll be off for some food shortly, will take a look later or maybe first tomorrow again.

Thx for helping today... I will look at the video later. :)

Hej
Jeg har før "rodet" med noget tilsvarende.. Jeg fandt det jeg lavede dengang og tilrettet det lidt... tag lige et kig på dette : 

https://1drv.ms/u/s!Aog7vvlTrHB6jJkJmqr5XF8CB2c22A

mvh martens

Tak Martens

Jeg har kigget på den og der er en del deri jeg kan bruge... skal nok få det på plads! :)

Also looked at the dB from Martens and it could be used as a start, and then you could build on that.

I havent gotten time to take a deeper look at it. I hope I will get there tonight.

I really do like you previous idea with orderlines - making it simpler to see, where stuff went from the inventory. Also the udlevering kunne laves, så der bare vælges en vare ad gangen og trykkes "Ud", indtil bestillingssedlen/ordren er løbet igennem.

Jeg tænker over det. :D

get back if you need some input

Yes a form for udleveringshistorik in a table :)

Ville det være bedre at dele varerne op i varegrupper og give hver en tabel - eller er det nok at have en type som et felt i tabellen?

You could have a table with varegruppe and your tuborg table has a field which references (relationship) to varegruppe table

for example:

tblProduct
ID (Primary key)
VareNummer (Unique index)
VareNavn
VareGruppeID (Foreign key relationship to tblVareGruppe)
Antal
ETC.....

tblVareGruppe
ID (Primary Key) = VareGruppeID
VareGruppeNavn
.
.

Yeah I was thinking of that - but last time I started building that way I ended up with 10 tables with loads of relations... I wanted to keep it simple... hmmmm

I have also made a field for size... that might be stupid aswell... could just type it in the name like: "Grøn Tuborg 33cl", "Coca-Cola 150cl" etc

So damn many choices to make.

and you could have a transactions table (in/out)

tblTransactions
ID (Primary key)
ProductID ((Foreign key relationship to tblProducts)
Antal (plus or minus)
.
.

This is just an idea, other fields may be necessary depending on you rneeds

"Yeah I was thinking of that - but last time I started building that way I ended up with 10 tables with loads of relations... I wanted to keep it simple... hmmmm"

"I have also made a field for size"
Well I was just writing a comment saying you could do that but maybe though, it could be a bit overkill. BUT... in the end its up to you ;-)

10 tables isnt many, I've worked with databases with 100's of tables...

Getting the design right from the start will make things easier later believe me.

If you still have the dB where you had 10 table then I could take a look and make some suggestions

https://drive.google.com/drive/folders/0B0CyEzYcGTg7dEx0ZlhoRUJLbmM?usp=sharing

You can have a look for sure... there might be some errors and relations that are awry...

Just had a very quick look and it doesn't look bad at all ;-) You already have the possibility for vare flow going out (bestilling + BestillingsDetalje)

Why dont you use this?

You'll need a form for making "Bestillinger" which will require some code to "Manipulere data.." as requested in your original question.
But if you can make a form so you can make a "Bestilling" then we can help with the code later.

Oh, just saw a problem!
The Bestilling table seems OK but BestillingsDetalje I think needs some changes, unless there us a reason why it is as it is?

BestillingsDetalje.Bestillingsnr should be the relationship to Bestilling, and maybe call it BestillingID

Right, off to see some TV, back tomorrow :-)

Seems I am getting it all mixed up now... *sigh*

The form I need to create to be able to type in orders, would have to do several things if I understand correctly.

First it should do a date/time automatically.
Next we need to select the Bar, the order is going our to.
Then the item, which requires a lookup in the existing varelager.
Then the quantity needed.

Lastly I have to add the "do it" button, which executes some code to make the order change the value in the tables selected.

My God... I'm far from understanding this. :D

Normally when you design a database like this you would have a requirement specification.

There are a couple of types for example functional and user, you'd normally involve users of the system to give there input.

EG:
http://www.ofnisystems.com/services/validation/functional-requirements/
http://www.ofnisystems.com/services/validation/user-requirement-specifications/

In your requirement you would need to see when making an order (Bestilling) it has to be attached (made to) a specific medarbejder. This tells me that you need a relationship to table Medarbejder, which you have. But in the text above (#36) you say it has to go to a Bar but there is no relationship to BarNavn!

And this is probably why your getting confused, I am too ;-)

So you have to first ensure that the Table design fulfils your requirements and if not make changes until it does.

IE. Is a Bestilling going to a bar or a medarbejder, or maybe even both.
Because you could say that a bestilling is going to Bar2 and it was made out to Brian Petersen (Onkel B)

Now if the order was made out to a medarbejder, does that medarbejder only have one arbejdsområde, which seems to be the case in your table design. Just a suggestion, I would change name of table from Barnavn to arbejdsomraad or something like that. If they always had the same work place then I would say its OK to have Medarbejder as a relationship in Bestilling, but if they could change work area then I would have another relationship in Bestilling to Arbejdsomraad (Barnavn).
Then you can ask yourself if its necessary to have an Arbejdsområde relationship in the Medarbejder table.  This could be their primary work area, so its to have one here too.

Not sure I understand the purpose of Salgsted, it seems its just additional information to Arbejdsomraad (Barnavn).

Just noticed that there is a field (Salgssted) in the Bestilling table, which confuses me even more.

Should it not be  Arbejdsomraad (Barnavn)?

Looking at the text in field Placering in the table Salgsted it seems Salgssted is a group of arbejdsomraader (Bar1 and bar2 in storteltet) for example.

So, is a bestilling going to a arbejdsomraader or a salgssted?

Now you could say that if there was a relationship between arbejdsomraader and Salgssted you would know which Salgsted received the bestilling if the bestilling was made out to an arbejdsomraader.

On the other hand, if you made a bestilling to a salgssted you would not know which arbejdsomraader received the bestilling.

All making sense now ? ;:-)

Hi Terry


I'm at work now, so I don't have Access here. I will try to enlighten you tho:

The initial database (version 1) got outta hand I think. The Medarbejder and Arbejdsområde really was not needed.

The important info is the Varelager, with varenavn, varetype, varestørrelse (could be in the name), varebeholdning, varestartbeholdning (to be able to calculate, if we need more of that item/running low).

Then there are the Bestillinger, which would contain orderinfo: varenavn, vareantal, barnavn (who ordered it), dato (when was the order placed).

Somehow that is all the info, that is really needed I think. You have to imagine a person coming over to our depot with a piece of paper containing the stuff, that is needed for a bar and then carrying it away a minute later. It's all very low tech and just needs to be able to handle the current status of all our wares. :)

Hi Brian
"It's all very low tech"

Well lets go after that then ;-)

VareLager:
VareID
VareNavn (including størrelse)
VareTypeID (Relation to Varetype.VaretypeID)

EG:
Cola 33cl
Cola 50cl

Varebeholdning (recalculated each time products go in and out)

Not sure why you need varestartbeholdning. You say (to be able to calculate, if we need more of that item/running low), but if varebeholdning is also startbehlodning and you add/subtract from that when you make a bestilling or receive new stock.


Bestilling: (would need to contain information specific for a single bestilling)
BestillingID
MedarbID (relationship to Medarbejder.MedarbejderID)
ArbejdsomraadID (relationship to primary key in table containing bar information)
Dato

Then BestillingDetaljer (products in bestilling)
ID
BestillingID (relation Bestilling.ID)
VareID (Realation to VareLager.VareID)
Antal


That's about it I think.

I am a bit confused. Do you mean I should make a table for the VareType?

The "arbejdsomraade" was actually to determine where the "medarbejder" was working and totally not needed. The "salgssted" has been renamed Barnavn - all wares go to a bar anyway.

So here we are...

I have 3 tables at the moment.

Varelager
Bestilling
BestillingDetaljer

Varelager will contain info on the inventory, while Bestilling will hold all orders, which are made up by BestillingDetalje? Right?

So my Bestilling needs a form, that prompts the user for what wares and quantity and where are they going.

The thing I am uncertain about is how I actually make the form do anything but add things to the already existing table...

I have added the version2 to the google folder, if you mind giving it a look?

No need to get confused Brain.

In  varelager I mentioned the field> VareNavn (including størrelse)
so they would be like this:
Cola 33cl
Cola 50cl

Does that clarify things?

If you get the tables made and then use the form wizard for Bestilling, it should make a main form with a sub form. Main form contains Bistilling information and sub form BestillingDetalje.

" ... am uncertain about is how I actually make the form do anything but add things to the already existing table..."

Once you can create Bestillinger with BestillingDetalje we can look at the code to subtract from Varelager. But we need to get Bistilling working first ;-)

Once we have bestilling working what would be best, to subtract each BestillingDetalje from Varelager antal when it gets entered, or when all BestillingDetalje have been entered?

Also, is it possible to delete or alter BestillingDetalje after they have been added? That would require further code to adjust Varelager antal accordingly.

Will look at version 2 if I can find it ...

Yeah, the size is a part of the names in Varelager.
The type however is not. Its beer, soda, liquer etc.

The bestilling should be locked after its been typed in... if someone screws the bestilling up, I will manually alter the table values. Im there the whole time anyway. :)

I will try the wiz on Bestilling and see what explodes!

OK, that looks wrong... hmmm

Also I added =Date() as default in the dato field - is there a command to get the date and time?

Ah, OK. I would have a type table then :-)

Comments to version 2.

Add the tables to the Relationships window and make the relationship's between the fields. This is necessary for the form Wizard to make form/sub forms.

Also, in Bestilling you can give the Dato field a default value. In design view there is a parameter "Default Value" set that to Now()
So when you add a new bestilling it automatically gets the data and time the record is made.

"The bestilling should be locked after its been typed in... "

Great, so no need to take into account alterations :-)

Just looking at the sub form which the wizard makes, not sure we can use it :-(

Back shortly...

Corrected some errors and added stuff - v3 is now online!

Shame about the wiz :(

Wizard works OK, must have done something wrong.

If you have made the relationships then try the wizard. You need to add all fields from both tables when asked.

Yeah I see the idea now... Bestilling will be a Mainform, with bestillingdetalje as subforms ... so there can be several of the bestillingdetaljer underneath the main form?

Now, for some magic here... the VareID in the sub form... how does one change that into a 2 step selection box? So I can choose type, then varenavn and lastly antal?

(I am so buying you a beer if we ever run into eachother!)

Yes, thats correct, this is what you wanted yes?

In design view for the bestilling form you need to go into the properties for the sub form to add some code.

click the top left "button of the sub form then right click on the mouse and choose form properties.

Now you should have the properties window open. Choose the event Tab and go down to AFTER INSERT. Now click the three small ... and choose "Code Builder"

When you do that you should have a coule of lines of cod like this

Private Sub Form_AfterInsert()

End Sub

You need to add this code BETWEEN the two line.

DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Varer SET Varebeholdning = Varebeholdning - " & Me.Antal & " WHERE VareID = " & Me.VareID
    DoCmd.SetWarnings True

so it looks like this

Private Sub Form_AfterInsert()

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Varer SET Varebeholdning = Varebeholdning - " & Me.Antal & " WHERE VareID = " & Me.VareID
    DoCmd.SetWarnings True
   
End Sub

"how does one change that into a 2 step selection box?"

Oh I forgot to mention that in my comments.

In table design for BestillingDetaljer you need to change the Data Type to "Lookup Wizard", its at the bottom.
Now follow the wizard. You should end up with a drop down when the table is open for entering data.

Now I think its easiest to delete th etwo forms you made and make them again, so that the Vare field is a drop down.

"and lastly antal?" ?

"Also I added =Date() as default in the dato field "

Now()

Hope your following all of this, its difficult to read all the comments here, maybe email directly?

ekspertenATsanthell.dk

AT = @

I am stopping shortly, will take a look tomorrow.

I think I missed something here... in the table BestillingDetalje I have:

ID
BestillingID
VareID
Antal

I need VareNavn instead - or do I just link the VareID to the Varer.VareID and get the name that way? And that is thru the Wiz I make a lookup... I will try

Hmm problem is, I have the VareID linked to VareID in Varer, so I cant change the type. Just delete the relation and try?

In table design for BestillingDetaljer you need to change the Data Type to "Lookup Wizard", its at the bottom.
Now follow the wizard. You should end up with a drop down when the table is open for entering data.

I seem to get the Type as the number it has (TypeID) instead of the name... getting a tad tired here aswell... I will send you a mail.

Thanks for the help so far.