Learning Access from scratch

[minn]

Hi, everyone

I start learning Access after posting in introduce yourself section and with help from GalaxiomAtHome.

I knew almost nothing about Access, but, have a crazy idea to program a sales model for my company as an accountant. after a couple of posts in my previous thread, I imported fundamental datas from our sales department from Excel to Access, the attached file tells the base story of what we are storing the data. hopefully, my learning course could assist people similar to me, and I am determined to learn what ever I can learn from everyone here. Thanks again to GalaxiomAtHome for bringing me into this new page where I think I will realise my dream of really using Access application as a pro

I tried to convert the attachment into mdb format, however, failed, coz my version of Access 2010 provides me the only option of access 2010 format, probably access 2007 can open it? apologize for the inconvenience.

I initially would like to post the link of my previous thread where you can see how I started, unfortunately, I got 5 posts so far ,and 10 posts are required to post a link. another apologize for the second inconenience


Kindest Regards
Minn

 

[Lightwave]

Minn I can't read this because I'm Access 2007 - If you have saved it in Access 2010 I don't think anyone except those with 2010 will be able to view it.

My background is in accountancy. It is an invaluable skill to be able to do accountancy and design databases. I would recommend it to anyone.

 

[minn]

Hi, Mark

Thanks for your drop by. I have some difficult to convert it to lower version of access for the moment, so, I attached the excel file for your info, it is the exact same thing in the accdb format. I am able to convert the excel file into 2003 version, so I guess you won't have trouble to open it, it is nice of you to import it into access 2007 if you don't mind ??

Yestoday, I moved a step forward to read some reading material downloaded as public information, I wish could copy paste here, but, have no idea if it is legitimate to do so. the material explains macros which could be used in Form. reading through the introduction, I found it seems necessary to learn VBA coding to achieve my goal, looks like a tough job for me. I don't know if there is any existing free code I can learn from to realise my first objective - to enter data from FORM into my sales table. I can understand(not programming) basic VBA code, that's my plus


Kindest Regards
Minn

Minn I can't read this because I'm Access 2007 - If you have saved it in Access 2010 I don't think anyone except those with 2010 will be able to view it.

My background is in accountancy. It is an invaluable skill to be able to do accountancy and design databases. I would recommend it to anyone.

 

[Lightwave]

I don't know if there is any existing free code I can learn from to realise my first objective - to enter data from FORM into my sales table. I can understand(not programming) basic VBA code, that's my plus

Several pointers on this...

First off as you are beginning don't worry too much initially about the finer intracacies of code. If you want to do professional applications you need to be able to code (VBA IS programming by the way) but you can get very very far using little or no code.

Secondly, I'm of the opinion especially for beginners that the starting point for beginners should be creating something (anything) that reduces the workload on the individual that will use it. It might not be the optimal it could be inefficiently designed but if it is more efficient than previous methods its a success. So as a beginner you want to be concentrating on things like being able to create a table / making a form out of that table / understanding relationships and how to set them up / understanding how to set the tab order on forms / navigation through forms. You simply must have these bases covered before you start looking at code.

So to start creating a simple form should not require ANY coding.

Design the table you want by taking all the column names in that excel sheet and using those as field names.

Then simply use the Form Wizard. The Form Wizard takes the user through the automatic creation of a simple form. It's layout is usually pretty appalling but you can then go in and edit it to improve things like the look size and the way in which you tab through the fields.

Once you've done that you will have lots more specific questions...
Like how do I use drop down boxes
Can I reference other tables in drop down boxes
How do I format a field to show particular currency
What abou sub forms...
etc...

Make a list of these individual questions and then solve them one at a time.

The first database which you make might have to be thrown out after a few months. But you will have learn't so much from it that you could potentially create a better version in weeks instead of months.

Try the above and then when you have the specific questions you can come back to here or just google the question and the answer will make much more sense.

The only other advice is... keep chipping away and be patient there's a lot to learn and you never feel like you know everything.

 

[minn]

Thanks, Mark, your pointers direct me into the correct path. I started with Form Wizard using fields from two different tables: 1] customer table, and 2] sales & debtor table.

The wizard popup a request to build relationship.

I managed to link customer fields from each table, seems so far so good. however, the link is established by customer fields which are not of primary key, I guess it is necessary to setup the relationship on the primary keys?? or, at least, in my impression, the link is supposed to be built up on the customer code.

I attached with a screenshot to show what I created as the link. Appreciate, if I may, tell me what correction I should make to get the setup good enough.




Kindest Regards
Minn

 

[minn]

Even though the form is able to display data from two tables with link by Customer field, the Q'ty & Inv Val in USD content only display a single line of record in the source table, I was expecting the sum of each customer, which is not the case for the moment.

I also attached the screenshot for your reference

Kindest Regards
Minn

 

[Lightwave]

The wizard popup a request to build relationship.

I managed to link customer fields from each table, seems so far so good. however, the link is established by customer fields which are not of primary key, I guess it is necessary to setup the relationship on the primary keys?? or, at least, in my impression, the link is supposed to be built up on the customer code.

Hi Minn not had a chance to look at your second post but on this point.

Yes you are correct normally you would set up the link between tables by using the primary key. Access will try and create the link good except it is not intelligent enough to do it correctly. It has simply looked at the two tables and matched the fields with the same name and then assumed that this is the link you want. Normally the line should be between your CID primary key in the customer table and a foreign key in the Sales and Debtor table. I can't quite tell from that picture but within the Sales and Debtor table you will need to have a field which will equate exactly to CID. If this doesn't already exist you will need to create it. This is essentially what would be defined as a foreign key and it is what is used to relate back the sales and debtor table to the customer. As a naming convention it is like the surname of people.

Setting up relationships should be easy - within the relationships window firstly delete the old relationship. (Use the mouse to select the line and then hit the delete button). Then use the mouse moving the cursor onto CID and then dragging across to the proper Foreign Key in the Sales and Debtor (the field indicated above which you may have created) table this will set the relationship.

It should be noted that you can relate fields such as in your picture but you will come across problems in the future. A customer's name is non specific so you may have more than one customer called FFAALLLL. The main reason for using a primary key is that the system prevents it from being repeated so that there is never any confusion about what sales relate to what customer.
An invoice number is a primary key... you don't want customers having repeated IDs for the same reasons you don't want invoice numbers being repeated.

 

[Lightwave]

Even though the form is able to display data from two tables with link by Customer field, the Q'ty & Inv Val in USD content only display a single line of record in the source table, I was expecting the sum of each customer, which is not the case for the moment.

I also attached the screenshot for your reference

Kindest Regards
Minn

The form wizard will allow you to create sub forms under header records. So the header record will be the customer and the sub form will be the sales and debtor record. This sub form can be made to show single forms or something called continuous forms (which is what you want)

What you have probably done is create a form JUST on the sales and debtor table.

It should be noted that even with a sub form a total is unlikely to be automatically generated. This is however easy to do but in the first instance try to create a form with a sub form once you have achieved that they we can look at the total issue.

I will be able to create a small example database for you at some point. If I can't do it this week (bit busy today and its New Year this weekend!) will get it to you beginning of next. It will just show you the proper relationships with a few records and I'll make a form with sub form and a total on it which should get you over this hurdle.

 

[minn]

Mark, as always, thank you a ton. I guess I managed to introduce a foreign key in the other related table, however, it was made in a different set of data base, which is only for trial and error purpose. I will do the same on the "Sales Model Practise", which I have been posting since, just to make the topic consistent. I think I will do it tomorrow.

It is really nice of you to spare me so much time on my study. In response to your next post, which I read with gratitude, I simply don't know how to thank you more, coz the holidays are very treasurous to you, I will feel guilty to take you so much time away. so, probably let me drill for a while to get something interesting, and then ask your guidance?? though I am very excited and very interested to see your sample.

By the way, I noticed the homework I am doing is in Access 2007 version, I don't know how, my Access is 2010, but, the database is indeed a A2007. maybe, I attach my exercise next time, and I may hope you can try to open it to see if it is compatible with your application? I really wish could share your minimal time to get the optimal knowledge by talking with you.

Sorry for the long post, this made me another guilty to let you read through some many words

Before ending this exciting year 2011, I wish you all the best, wish you and your family have a prosperious year 2012, all the lucky be with you.


Kindest Regards
Minn

Hi Minn not had a chance to look at your second post but on this point.

Yes you are correct normally you would set up the link between tables by using the primary key. Access will try and create the link good except it is not intelligent enough to do it correctly. It has simply looked at the two tables and matched the fields with the same name and then assumed that this is the link you want. Normally the line should be between your CID primary key in the customer table and a foreign key in the Sales and Debtor table. I can't quite tell from that picture but within the Sales and Debtor table you will need to have a field which will equate exactly to CID. If this doesn't already exist you will need to create it. This is essentially what would be defined as a foreign key and it is what is used to relate back the sales and debtor table to the customer. As a naming convention it is like the surname of people.

Setting up relationships should be easy - within the relationships window firstly delete the old relationship. (Use the mouse to select the line and then hit the delete button). Then use the mouse moving the cursor onto CID and then dragging across to the proper Foreign Key in the Sales and Debtor (the field indicated above which you may have created) table this will set the relationship.

It should be noted that you can relate fields such as in your picture but you will come across problems in the future. A customer's name is non specific so you may have more than one customer called FFAALLLL. The main reason for using a primary key is that the system prevents it from being repeated so that there is never any confusion about what sales relate to what customer.
An invoice number is a primary key... you don't want customers having repeated IDs for the same reasons you don't want invoice numbers being repeated.

 

[Lightwave]

Best of luck to you as well Minn talk to you next week.

 

[Lightwave]

Take a look at this Minn

Really basic little sales ledger database that will show you how you can get totals added in the sub form of a header record.

In access 2003 format which I think 2010 will read

Double click on the F001 Customer to get the form which has some helpful hints written on it.

 

[minn]

Good Morning, Mark, as always, thanks very much for the beautifully laid out sales ledger .

I looked into the forms design view, and changed the design from continuous subform to a speadsheet view of F002, it changes accordingly in the form 001, really fun experience.

It seems the formula of addition =Sum(Nz([Amount]))
code is the same between F001 and F002, both are written in the form footer. this gives me some idea that the addition applies only to the line items displayed in the form body. I will try to follow this method to see if I can get the same result.

Before I do the above mentioned excerise, a quick question: I tried to change the CID number in F001, wanted to see the second customer data, and I got nothing changed. Maybe, it is not defined to do so yet? just curious about it, maybe I can learn this from you next time.
[oops, I clicked the record number in the form footer, it changes accordingly to the second customer; initially, I was changing the number in the CID field, and got nothing change]

This is really fantastic, Mark !


Kindest Regards
Minn

Take a look at this Minn

Really basic little sales ledger database that will show you how you can get totals added in the sub form of a header record.

In access 2003 format which I think 2010 will read

Double click on the F001 Customer to get the form which has some helpful hints written on it.

 

[minn]

Hi, Mark, I think I am able to make addition in the form now, attached with the screencatch to show the total in a field I made.

Meanwhile, in the same form, you may notice that I can not get the exchange rate successfully into the subform field under the title of Ex Rate; the second and third attachments show how I tried to retrieve the exchange rate data from tblCurrency into the subform;

I was thinking to get the exchange rate by referring to the currency ID (CURIDFK), but I think I got into a wrong way of doing it. I don't know what is the correct method, please mind telling me how to do it? thank you

Kindest Regards
Minn

 

[Lightwave]

Hi Minn

Right glad you understood.

As you have guessed you are getting an error on the exchange rate because there is problems relating the exchange rate to the currency to be used.

What I would probably do is have a further table called Currency with all the exchange rates. (Sounds as if you already have this)

I would then probably make a query up with the invoice transaction and the currency exchange rate linked combined. So on running the query you should have all the invoice transactions along with their exchange rates.

You can then make a Form and Sub form but this time rather than using the table invoice as the source for the countinous subform you can use the query with the exchange rates as the source. There will have to be a relationship between the Invoice and the Currency Exchange rate tables but it sounds as if you have either done that already or are considering doing it anyway.

Try and interpret my explanation in the first instance. If you don't get it working post back and I'll update the database with an example.

And yes Sum(NZ(Inv_Val)) should work as you have guessed that pattern should work for numeric fields...

PS I would avoid trying to change the CID number. All primary key fields should be fixed and if possible made so that they auto generate. They should be created once on the initial record and never changed again. Using the navigation buttons at the bottom of the form will allow you to scroll through the records seeing the changing listed invoices as you go.

 

[minn]

Good Morning, Mark

It is really amazing, by following your instruction, I can make the desired form now, please see the attached screenshot.

Just wondering what is the exact reason I have to build my form upon a query, instead of directly using table; probably this is a question regarding to the benefit utilizing a query??

two minor problem in the screenshot
1] I can not put a label on the blank box left to the sum of inv value. I tried to write the label content directly into the box, but, not workable. I noticed all of the label are automatically created when making a form based on table or query.

2] I don't know how to hide the CIDFK column. CIDFK field is a must for me to link my customer table with the query I made for this particular form design. can I simply delete this field in the design view of the form?

I managed to create the form though, I still feel not fully understanding the concept of relationship. [Edit] In the next post, I submitted a relational trouble I am in.


Regarding to the primary key, I understood now it can not be changed, as it is given by Access, I can change the rest field in the form and send the modification back to the related table. what if I want to enter new data into the table and not give user a chance to alter previous records from a form?

Sorry for bringing you so many questions. I will try my best to learn from text book, the challenge is that I can not make progress learning textbook at this stage, I knew it as I tried several times previously. I think this will continue untill I really grasped the basics, then, the textbook will show its power to help with. I don't know if it is true, however, it is what in my mind, I guess?? I really realise your instruction is much more effective than any textbook on earth for the moment,although it doesn't mean that I want to totally realy on your help coz your time is so treasurous that I feel I don't have the right to spare too much ......


Kindest Regards
Minn

 

[minn]

Hi, Mark

When working with query based on more than 2 tables, I got SQL statement not executable due to ambiguous outer joins. This is really a complicated concept to me. I tried to understand it by google some information, here is the summary info I got

Quote Begin
++++++++++++++++++++++++++++++++++++++

The following combinations of joins can produce more than one result and will generate an "ambiguous outer joins" message:​

Consultants INNER JOIN Active Consultants RIGHT OUTER JOIN Projects​

Consultants LEFT OUTER JOIN Active Consultants INNER JOIN Projects​

Consultants LEFT OUTER JOIN Active Consultants RIGHT OUTER JOIN Projects​

++++++++++++++++++++++++++++++++++++++
Quote End

in my case, it is probably the problem of the last one {

Consultants LEFT OUTER JOIN Active Consultants RIGHT OUTER JOIN Projects​

}

Here is the narrative of the relationship I wish to build among the tables:
1. I have a product ( which is actually a product category,but, in my table, I called them product )
2. The product has varieties
3. Each variety is made by different length of the stem , which is defined as product specific in the table I made; probably the best description is SKU here
4. and lastly, these product varieties will be packed in carton box, whereas the box has different size, meaning different number of stems will pack into corresponding carton box

And, the packing list will display variety, Lenght of the variety, Quantity of the Variety in each box, and number of carton box with tracking number box by box; whereas, the number of stems in the same size of carton box could vary

I don't know how to sort out my relationships setup in trouble as attached. not sure if you have time to help take a look ???


Kindest Regards
Minn



Another info describing the situation I am in

Quote Begin
++++++++++++++++++++++++++++++++++++
OK : Consultants ---- Active Consultants ---- Projects
OK : Consultants ---- Active Consultants ---> Projects
ERR: Consultants ---- Active Consultants <--- Projects

ERR: Consultants ---> Active Consultants ---- Projects
OK : Consultants ---> Active Consultants ---> Projects
ERR: Consultants ---> Active Consultants <--- Projects

OK : Consultants <--- Active Consultants ---- Projects
OK : Consultants <--- Active Consultants ---> Projects
OK : Consultants <--- Active Consultants <--- Projects
++++++++++++++++++++++++++++++++++++++++++++++++++
Quote End

 

[Lightwave]

Afternoon Minn

OK just seen that other post let me deal with your first questions in this post and then I will take a stab at your outer join problems in subsequent posts.

In Access there are multiple ways of doing similar things as such making a form based on a query is only one way of achieving the task that you requested. I consider it to be one of the most intuitive ways of achieving what you requested so for ease I chose that way to explain to you as well. It can also be achieved by using combo boxes (these are a specific variant of a text box)

This is probably a good time to explain to you about objects. When you get a database wizard to create a form for you it automatically creates items and places them on a screen in something users get to know as a form. The things it places on the forms are called objects. Each object generally has a type and a set of properties. Anything that contains text or numbers is generally a type of text box. Text boxes themselves have different variants the main ones being a standard text box and the alternate a drop down or combo box. Other types of objects include but are not limited to things like labels, radio buttons and command buttons.

Nearly all forms of object orientated programming use similar objects each with a set of inherent properties. For example this forum has objects all over the place. The buttons such as (post quick reply) and post (reply) would be called command buttons in Access. Things like the text size is a property of the command buttons. Drop down boxes in Access are similar to the objects on this forum page where there are downward triangles which when clicked reveal further options. Combi boxes are extremly useful in making nice looking applications and they are quite easy to get working but first we must sort out your other problems before getting into the finer details of improving user experience.

Within Access 2003 there is an option within the design view called the toolbox. In Access 2003 this can be free floating - I suspect in Access 2010 it may be a menu I can "attach" the 2003 tool box to a bar as if it is a menu. When designing forms this menu is very important.

Right I've just loaded Access 2007 (I do most of my design in Access 2003 but I do have Access 2007 and in design view many of what was called tools in Access 2003 is now called controls in Access 2007. The menu doesn't seem to float as a pop up window either so ignore that part of the above explanation. All the symbols are the same though. So assuming Access 2010 is very similar to 2007 you want to click on the item marked Aa Label - this will allow you to draw on the form and type in whatever text you want.

OK so once you understand the concept of objects all objects have properties and this is where your second question comes in. In my example database I didn't hide CIDFK because I wanted you to see what was going on in the background. In order for everything to work Access needs those fields on the form but you are quite right they have no real benefit to the user. Object properties include everything - colour / height / font / text weight / text size etc.

Click on the object you wish to alter and right click. A menu should appear at the bottom of which is an option called Properties. Click on this and just take a minute to look at all the different property options you see.. The selection will depend on what object you have selected but there will be many that you can predict what will change if you alter them and some you won't understand. You have already altered things within the property sheet of the form (when you altered continuous form to datasheet). You see everything has properties even the form. Some properties are specific to certain objects like you will not have the option to change a text box to a continuous form because it wouldn't make sense while text size would not be relevant to a forms property which only refers to the blank background and the window.

Property options are pre-defined in Access and are specific to differing types of objects. It so happens the CIDFK is a text box which has a property called Visible. Select the text box enter its properties look for [Visible] property and use the (Drop down box) to alter from yes to no. Save the form and view it and the CIDFK should now disappear.

Fiddle and Twitch alter colours sizes fonts etc... as you see fit.

 

[Lightwave]

Minn

With regard to the error on the join.

Errors in joins occur because you have comprimised the mathematical integrity of a statement. SQL is based on set mathematics... think algebra.

And while it is possible to create sometypes of algebraic statements they may make no sense..

You can for instance have two sets and say set A is the set of parents of B. Indicate to me how many children set B has. This would make no sense as you first state that set B are children. This is the kind of time when Access will give you these errors.

Within queries you can alter the flavour of joins. You can go into the query and move the cursor to the line which has been created in the tables and right click. This will allow you to alter the direction of the joins. Quite often by altering the "flavour of the join" you can get the queries to work. Experiment initially with this and altering the types of joins to see the difference between the different types of joins. I still get a bit confused about inner an outer joins.

 

[Lightwave]

Just looked at the png file. In the first instance you might want to delete the link between tblPackSpec and tblVareity and then run... That looks wrong to me

Access sometimes creates these links automatically when you place tables in queries which break its own joining rule..

It's only a computer program after all..

 

[minn]

Hi, Mark

Yeah, I noticed the toolbox being named as controls in 2007 ( I mistake my Access 2007 as Access 2010, this is wierd part of my installation, what I installed is Ms Office 2010,but, the access on my computer is Access 2007). thank you for the brilliant explanation about objects

I think I didn't use the text box correctly, after you explained the concept of objects and the way of using it, I realise label and text box are different things; what I previously did wrong is I just used half of the text box, I used it as a label objects, stupid me

It is cute to hide the CIDFK ; by the way, I mistakenly deleted CID and CIDFK in the form, and the relationship seemingly still exists; then, I got a chance to look into the properties of the control frame of the subform, and found that the underlying link is built between the links of parent field and the child field under data view

lastly, about the relationship you recommend to delete, I followed up; I forgot what I tried to do yestoday using more than 2 tables, so, can not see if it worked after the deletion, will let you know once I remembered what to do.


Kindest Regards
Minn

Afternoon Minn

OK just seen that other post let me deal with your first questions in this post and then I will take a stab at your outer join problems in subsequent posts.

In Access there are multiple ways of doing similar things as such making a form based on a query is only one way of achieving the task that you requested. I consider it to be one of the most intuitive ways of achieving what you requested so for ease I chose that way to explain to you as well. It can also be achieved by using combo boxes (these are a specific variant of a text box)

This is probably a good time to explain to you about objects. When you get a database wizard to create a form for you it automatically creates items and places them on a screen in something users get to know as a form. The things it places on the forms are called objects. Each object generally has a type and a set of properties. Anything that contains text or numbers is generally a type of text box. Text boxes themselves have different variants the main ones being a standard text box and the alternate a drop down or combo box. Other types of objects include but are not limited to things like labels, radio buttons and command buttons.

Nearly all forms of object orientated programming use similar objects each with a set of inherent properties. For example this forum has objects all over the place. The buttons such as (post quick reply) and post (reply) would be called command buttons in Access. Things like the text size is a property of the command buttons. Drop down boxes in Access are similar to the objects on this forum page where there are downward triangles which when clicked reveal further options. Combi boxes are extremly useful in making nice looking applications and they are quite easy to get working but first we must sort out your other problems before getting into the finer details of improving user experience.

Within Access 2003 there is an option within the design view called the toolbox. In Access 2003 this can be free floating - I suspect in Access 2010 it may be a menu I can "attach" the 2003 tool box to a bar as if it is a menu. When designing forms this menu is very important.

Right I've just loaded Access 2007 (I do most of my design in Access 2003 but I do have Access 2007 and in design view many of what was called tools in Access 2003 is now called controls in Access 2007. The menu doesn't seem to float as a pop up window either so ignore that part of the above explanation. All the symbols are the same though. So assuming Access 2010 is very similar to 2007 you want to click on the item marked Aa Label - this will allow you to draw on the form and type in whatever text you want.

OK so once you understand the concept of objects all objects have properties and this is where your second question comes in. In my example database I didn't hide CIDFK because I wanted you to see what was going on in the background. In order for everything to work Access needs those fields on the form but you are quite right they have no real benefit to the user. Object properties include everything - colour / height / font / text weight / text size etc.

Click on the object you wish to alter and right click. A menu should appear at the bottom of which is an option called Properties. Click on this and just take a minute to look at all the different property options you see.. The selection will depend on what object you have selected but there will be many that you can predict what will change if you alter them and some you won't understand. You have already altered things within the property sheet of the form (when you altered continuous form to datasheet). You see everything has properties even the form. Some properties are specific to certain objects like you will not have the option to change a text box to a continuous form because it wouldn't make sense while text size would not be relevant to a forms property which only refers to the blank background and the window.

Property options are pre-defined in Access and are specific to differing types of objects. It so happens the CIDFK is a text box which has a property called Visible. Select the text box enter its properties look for [Visible] property and use the (Drop down box) to alter from yes to no. Save the form and view it and the CIDFK should now disappear.

Fiddle and Twitch alter colours sizes fonts etc... as you see fit.