Access form building
March 3, 2006 10:26 PM Subscribe
MS Access XP: Say I have 3 tables.
I'm trying to migrate from an excel spreadsheet(s) to a database and my boss wants me to use access. But he's asking me something that I don't know how to do yet. I know it's probably something simple, but I'm having problems here.
Table.Customers with the fields:
CustomerID | CustomerName* | CustomerAddress | CustomerEmail |
Table.Projects with the fields:
ProjectID | ProjectLocation* | ProjectDetail1 | ProjectNote |
Table.Reports with the fields:
ReportID | ReportName* | CustomerID | ReportID | ProjectID | ReportStatus1 | ReportStatus2 | ReportStatus3 |
*Unique Fields
How do I get a form to display ReportName | CustomerName | CustomerEmail | ProjectLocation | ProjectNote | ReportStatus1 | ReportStatus2 | ReportStatus3 |
Where CustomerName and ProjectLocation can be selected with dropdown boxes, and not editable so that CustomerEmail and ProjectNote show the fields related -- which are also not editable. Everything else would be editable.
The lookup wizard doesn't seem to do what I want it to do for this. I have a sneaking suspicion that it has something to do with a query and a form.
Also are there any websites/communities devoted to this kinda thing?
I'm trying to migrate from an excel spreadsheet(s) to a database and my boss wants me to use access. But he's asking me something that I don't know how to do yet. I know it's probably something simple, but I'm having problems here.
Table.Customers with the fields:
CustomerID | CustomerName* | CustomerAddress | CustomerEmail |
Table.Projects with the fields:
ProjectID | ProjectLocation* | ProjectDetail1 | ProjectNote |
Table.Reports with the fields:
ReportID | ReportName* | CustomerID | ReportID | ProjectID | ReportStatus1 | ReportStatus2 | ReportStatus3 |
*Unique Fields
How do I get a form to display ReportName | CustomerName | CustomerEmail | ProjectLocation | ProjectNote | ReportStatus1 | ReportStatus2 | ReportStatus3 |
Where CustomerName and ProjectLocation can be selected with dropdown boxes, and not editable so that CustomerEmail and ProjectNote show the fields related -- which are also not editable. Everything else would be editable.
The lookup wizard doesn't seem to do what I want it to do for this. I have a sneaking suspicion that it has something to do with a query and a form.
Also are there any websites/communities devoted to this kinda thing?
It's not difficult, but I think it is useful to get a general grasp on how this works with Access, because you'll need it in the future. I liked Access Database Design and Programming.
Maybe this Microsoft tutorial is also useful. See also: Microsoft Access Tutorials (or other results from this google search).
Good luck. Access is lots of fun.
posted by davar at 1:37 AM on March 4, 2006
Maybe this Microsoft tutorial is also useful. See also: Microsoft Access Tutorials (or other results from this google search).
Good luck. Access is lots of fun.
posted by davar at 1:37 AM on March 4, 2006
i don't know about access, but the sql to get the fields you want would be something like
select r.ReportName, c.CustomerName, c.CustomerEmail, p.ProjectLocation. p.ProjectNote, r.ReportStatus1, r.ReportStatus2, r.ReportStatus3
from Customers as c, Projects as p, Reports as r
where r.CustomerId=c.CustomerId
and r.ProjectId=p.ProjectId;
you can also write the query using "joins" if you need to worry about null values (or prefer that style) - i think the form above is easier to understand. the Id fields are use to connect rows in different tables (and so it's important that they be unique - it's OK to have two customers called "John Smith" as long as they have different CustomerId values).
maybe that's some help.
posted by andrew cooke at 6:07 AM on March 4, 2006
select r.ReportName, c.CustomerName, c.CustomerEmail, p.ProjectLocation. p.ProjectNote, r.ReportStatus1, r.ReportStatus2, r.ReportStatus3
from Customers as c, Projects as p, Reports as r
where r.CustomerId=c.CustomerId
and r.ProjectId=p.ProjectId;
you can also write the query using "joins" if you need to worry about null values (or prefer that style) - i think the form above is easier to understand. the Id fields are use to connect rows in different tables (and so it's important that they be unique - it's OK to have two customers called "John Smith" as long as they have different CustomerId values).
maybe that's some help.
posted by andrew cooke at 6:07 AM on March 4, 2006
I don't know how far you've gotten, but here's a start at the very beginning explanation that I threw together on one of those new google pages.
posted by bim at 7:33 AM on March 4, 2006
posted by bim at 7:33 AM on March 4, 2006
Your third table is kind of a mess, I think. It seems like it's trying to function as both a join table and a query at the same time.
posted by empath at 8:47 AM on March 4, 2006
posted by empath at 8:47 AM on March 4, 2006
The tables are fine. It doesn't matter if there's some repetition across tables. When it's all put together, we'll just uncheck certain columns to avoid printing and/or looking at repeats. And in a sense, it might provide a little check to see if anything looks wacky.
Ultimately, I'd arrange the window with table 3 in the middle and the other tables to the left and right. Then you have to join table 2 with table 3 on project ID and join table 1 and table 3 on customer ID. The joins are just done by dragging and dropping. Then we can choose what colums we want to retain for final display by checking or unchecking the little box in each column of the query view.
The final result can also be exported into a plain old excel spreadsheet if desired too. You can throw on some pretty formatting in excel if you really want to go wild. Add colors to the table borders and such. Pull the spreadsheet into word if you want. Whatever.
posted by bim at 9:30 AM on March 4, 2006
Ultimately, I'd arrange the window with table 3 in the middle and the other tables to the left and right. Then you have to join table 2 with table 3 on project ID and join table 1 and table 3 on customer ID. The joins are just done by dragging and dropping. Then we can choose what colums we want to retain for final display by checking or unchecking the little box in each column of the query view.
The final result can also be exported into a plain old excel spreadsheet if desired too. You can throw on some pretty formatting in excel if you really want to go wild. Add colors to the table borders and such. Pull the spreadsheet into word if you want. Whatever.
posted by bim at 9:30 AM on March 4, 2006
You're not getting very good advice here. It's a very bad idea to have the same data in more than one field. If it changes, you may not correctly change it in all the places it needs to be changed, and this can give you annoying problems later. It's easy to use one value in one form, but another value in another form. When you're building the second form a year after you did your first, it's really, really easy to make very bad mistakes and corrupt your data completely. If you have two fields in the database that are supposed to be the same, but you blew it and changed only one, how on earth are you supposed to figure out which one is right?
You need to look up and get familiar with the concepts of 'one to many' and 'many to many' joins. Customers are unique, for example, but they may have more than one address. (a one to many join). Each address will have only one zip code, but zip codes will have many addresses. (one to many, but in this case, the zip code is the unique value.)
One-to-many joins are specified by listing a foreign key in a table. In an addresses table, you might have 'street number', 'street name' and then a POINTER TO a zip code... and in the zip code table, you have the name of the city and state. (The zip code itself can be your foreign key.) I haven't used Access in many years, but I'm almost sure it's set up in the table layout editor, and is very easy.
Many-to-many relationships are much harder, and require an intermediate join table. Orders, for example, can come from many different customers, and any given order can have many different items. Your Orders table becomes a many-to-many join between customers and items.
It's also important to understand 'data normalization', which sounds scary and difficult, but actually isn't. It's all about putting data in just one place, and never repeating it. (That means you can change it in just that one place and it's fixed everywhere at once.) It also saves space and improves speed. That thing about putting zip codes into a separate table is an example of that. If 94952 suddenly becomes part of Santa Rosa instead of Petaluma, instead of having to search and change thousands (or millions) of records, which could require hours to run (and require downtime), you change it just once. Every record in your database is instantly fixed. No downtime, practically no work.
It'll probably take you a week or two of study, off and on, to wrap your head around these ideas. If you take the time to learn it right, however, you will save yourself endless trouble later. Access is NOT a spreadsheet, and if you try to treat it like one, you're going to run into all kinds of problems.
The Access 2.0 manual was actually an excellent source for how to do table design. Once upon a time, believe it or not, you actually got manuals with complex pieces of software like Access.
You might want to look through your CDs and see if you have electronic documentation. With the amount of effort they must have put into writing those early (and excellent) manuals, I would be surprised if they weren't still available to you in some form.
Again, if you take the time to learn this now, you will avoid falling into an amazing number of possible design errors, many of which will force you to scrap your entire database and start over.
Databases are powerful tools. If you take a little time to understand how they really work, they can be incredibly useful. If you don't take the time to learn them, they can bite you badly.
posted by Malor at 1:46 PM on March 4, 2006 [1 favorite]
You need to look up and get familiar with the concepts of 'one to many' and 'many to many' joins. Customers are unique, for example, but they may have more than one address. (a one to many join). Each address will have only one zip code, but zip codes will have many addresses. (one to many, but in this case, the zip code is the unique value.)
One-to-many joins are specified by listing a foreign key in a table. In an addresses table, you might have 'street number', 'street name' and then a POINTER TO a zip code... and in the zip code table, you have the name of the city and state. (The zip code itself can be your foreign key.) I haven't used Access in many years, but I'm almost sure it's set up in the table layout editor, and is very easy.
Many-to-many relationships are much harder, and require an intermediate join table. Orders, for example, can come from many different customers, and any given order can have many different items. Your Orders table becomes a many-to-many join between customers and items.
It's also important to understand 'data normalization', which sounds scary and difficult, but actually isn't. It's all about putting data in just one place, and never repeating it. (That means you can change it in just that one place and it's fixed everywhere at once.) It also saves space and improves speed. That thing about putting zip codes into a separate table is an example of that. If 94952 suddenly becomes part of Santa Rosa instead of Petaluma, instead of having to search and change thousands (or millions) of records, which could require hours to run (and require downtime), you change it just once. Every record in your database is instantly fixed. No downtime, practically no work.
It'll probably take you a week or two of study, off and on, to wrap your head around these ideas. If you take the time to learn it right, however, you will save yourself endless trouble later. Access is NOT a spreadsheet, and if you try to treat it like one, you're going to run into all kinds of problems.
The Access 2.0 manual was actually an excellent source for how to do table design. Once upon a time, believe it or not, you actually got manuals with complex pieces of software like Access.
You might want to look through your CDs and see if you have electronic documentation. With the amount of effort they must have put into writing those early (and excellent) manuals, I would be surprised if they weren't still available to you in some form.
Again, if you take the time to learn this now, you will avoid falling into an amazing number of possible design errors, many of which will force you to scrap your entire database and start over.
Databases are powerful tools. If you take a little time to understand how they really work, they can be incredibly useful. If you don't take the time to learn them, they can bite you badly.
posted by Malor at 1:46 PM on March 4, 2006 [1 favorite]
As someone who had to spend several weeks unscrambling a customer's horribly designed contact database at a previous job, I agree with Malor whole heartedly.
Look up Data Normalization now before it's too late. It's really not that hard to do it right.
posted by empath at 1:56 PM on March 4, 2006
Look up Data Normalization now before it's too late. It's really not that hard to do it right.
posted by empath at 1:56 PM on March 4, 2006
Ahem...I beg to differ with Malor & empath.
Having spent over 15 years doing government data analysis and using everything from statistical packages to databases to spreadsheets to mapping software -- and having used a million different kinds of data from a million different places -- I have a pretty good idea how things work.
But by all means, bigmusic should get some books, study up normalization and a million other things to get started. That will give bigmusic even more time to worry about the precise way to proceed before even touching the keyboard.
Of course, by then s/he will be unemployed, but what the hell. Right?
Never mind that it doesn't sound like the poster is a computer programmer in some big company. Otherwise, they would already know how to use a database.
Quit being alarmist, folks. The computer won't explode if a person digs right in and starts to learn by doing. In fact, that's the only way to learn, IMHO. But whatever.
Nothing like reinventing the wheel.
Good luck!
posted by bim at 2:33 PM on March 4, 2006
Having spent over 15 years doing government data analysis and using everything from statistical packages to databases to spreadsheets to mapping software -- and having used a million different kinds of data from a million different places -- I have a pretty good idea how things work.
But by all means, bigmusic should get some books, study up normalization and a million other things to get started. That will give bigmusic even more time to worry about the precise way to proceed before even touching the keyboard.
Of course, by then s/he will be unemployed, but what the hell. Right?
Never mind that it doesn't sound like the poster is a computer programmer in some big company. Otherwise, they would already know how to use a database.
Quit being alarmist, folks. The computer won't explode if a person digs right in and starts to learn by doing. In fact, that's the only way to learn, IMHO. But whatever.
Nothing like reinventing the wheel.
Good luck!
posted by bim at 2:33 PM on March 4, 2006
If I understand you right, this is pretty straightforward. First, get the excel tables into access. This is easy. Open a new access database, then just go File>Get External Data>Import, and then find your excel files and follow the directions.
Now, make a new query. Get the design view. Add the three tables to the query. This will now look like three rectangles that are representations of the three tables, with all the field names in them.
Click on and drag across the common fields, which creates a join across tables. For example, CustomerID in the first table to CustomerID in the third one. And ProjectID to ProjectID. The tables will now be connected with lines from those fields.
Now, you have to get the field names you ultimately want to display down to the grid area. To do this you can click and drag, or just double click on the fields. Keep doing this til you have all the fields you want displayed.
Run the query, just to test it to make sure it gives you the information you want. If so, save it and close it and go to make a new form using the query as a source. Put all the fields you want in the form. To make them non-editable, just open the text box properties and, on the Data tab, just Lock it, or un-Enable it. To make a dropdown combo box that does what I think you want, you'll need to add a little VBA coding.
Open the tool box, and put in a combo box onto your form. There's a wizard that will walk you through the initial steps, especially where to get the data you are looking to populate the combo box with. This will turn out to be a query that is the Row Source, but just follow the wizard for these steps. Then, once you're finished with that, you need to have the combo box value pull in the correct record. To do this, go to the Combo Box properties, and under the Event tab, click to the right of the After Update field, and put in an Event Procedure. Then, click on the three buttons to the right of that field, and it'll take you to the VBA editor.
Here's an example of the code you want. The first and last lines will be supplied to you by Access. You type in the rest, substituting the names of the combo box and the ID field.
*******
Private Sub nameofcombobox_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[yourIDfield] = '" & Me![nameofcombobox ] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******
That one line that begins with rs.FindFirst formats in a difficult to read way here. First of all, get it all on one line, or use the line continuation string, which is a space and an underscore at the end of the first line. Putting the quote types in curly brackets, it should read like this:
rs.FindFirst {doublequote}[yourIDfield]= {singlequote}{doublequote} & Me![comboboxname] & {doublequote}{singlequote}{doublequote}
Hope I understood you right - good luck, and just email me if this is unclear.
posted by jasper411 at 4:15 PM on March 4, 2006
Now, make a new query. Get the design view. Add the three tables to the query. This will now look like three rectangles that are representations of the three tables, with all the field names in them.
Click on and drag across the common fields, which creates a join across tables. For example, CustomerID in the first table to CustomerID in the third one. And ProjectID to ProjectID. The tables will now be connected with lines from those fields.
Now, you have to get the field names you ultimately want to display down to the grid area. To do this you can click and drag, or just double click on the fields. Keep doing this til you have all the fields you want displayed.
Run the query, just to test it to make sure it gives you the information you want. If so, save it and close it and go to make a new form using the query as a source. Put all the fields you want in the form. To make them non-editable, just open the text box properties and, on the Data tab, just Lock it, or un-Enable it. To make a dropdown combo box that does what I think you want, you'll need to add a little VBA coding.
Open the tool box, and put in a combo box onto your form. There's a wizard that will walk you through the initial steps, especially where to get the data you are looking to populate the combo box with. This will turn out to be a query that is the Row Source, but just follow the wizard for these steps. Then, once you're finished with that, you need to have the combo box value pull in the correct record. To do this, go to the Combo Box properties, and under the Event tab, click to the right of the After Update field, and put in an Event Procedure. Then, click on the three buttons to the right of that field, and it'll take you to the VBA editor.
Here's an example of the code you want. The first and last lines will be supplied to you by Access. You type in the rest, substituting the names of the combo box and the ID field.
*******
Private Sub nameofcombobox_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[yourIDfield] = '" & Me![nameofcombobox ] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******
That one line that begins with rs.FindFirst formats in a difficult to read way here. First of all, get it all on one line, or use the line continuation string, which is a space and an underscore at the end of the first line. Putting the quote types in curly brackets, it should read like this:
rs.FindFirst {doublequote}[yourIDfield]= {singlequote}{doublequote} & Me![comboboxname] & {doublequote}{singlequote}{doublequote}
Hope I understood you right - good luck, and just email me if this is unclear.
posted by jasper411 at 4:15 PM on March 4, 2006
As someone who earned a living doing contract DB work for a couple of years, I urge you to listen very carefully to Malor's advice. You can definitely spend a couple of weeks reading up on all this stuff, but the most important concepts (joins, relationships, one-to-many, normalisation, intersecting identities ("intermediate join table"), etc) you can figure out in a day or two. You can probably find a really good intro-to-DBs textbook at your local library.
bim: If you end up employed after spending a day or two researching the correct way to do the work your employer has asked you to do, you needed a new employer anyway.
Sure, go for the hands on and play with it, but at least spend a day reading up on the core concepts and be prepared to chuck the work you did before you understand these concepts. Once you understand those basics, hands on fiddling will have you learning Access probably faster than any other method.
posted by polyglot at 6:36 PM on March 4, 2006
bim: If you end up employed after spending a day or two researching the correct way to do the work your employer has asked you to do, you needed a new employer anyway.
Sure, go for the hands on and play with it, but at least spend a day reading up on the core concepts and be prepared to chuck the work you did before you understand these concepts. Once you understand those basics, hands on fiddling will have you learning Access probably faster than any other method.
posted by polyglot at 6:36 PM on March 4, 2006
In my experience and judging from folks I've worked with who wanted help, the best thing that you can do for someone is to lead them totally through the process once to get to the final result. That's what I'm frequently asked to do. And the person usually says, "So why didn't they (the manuals and ACCESS help) just tell me to do that.
You can read those ACCESS books until the cows come home, but if you are unfamiliar with databases, you're going to have a very difficult time figuring out what to put where.
It makes no sense to talk about types of joins until they can even see what a join is and how to get one.
I am by no means advocating sloppy work. I myself do meticulous work. But it's also important to get the process moving. One example is better than a thousand pages of manuals, IMHO. And nothing is written in stone. People can learn from their mistakes and modify thngs.
And, perhaps I'm wrong, but I think the poster is at a very basic level. Talking about visula basic and such seems way ahead of the game. It's not even clear if the poster really even needs a form. They may just need a result i.e. just run the query once they get it put togther in a suitable fashion.
Anyway, we may just have to agree to disagree about technique and such. No big deal. I'm sure things are in good hands with Jasper.
posted by bim at 7:02 PM on March 4, 2006
You can read those ACCESS books until the cows come home, but if you are unfamiliar with databases, you're going to have a very difficult time figuring out what to put where.
It makes no sense to talk about types of joins until they can even see what a join is and how to get one.
I am by no means advocating sloppy work. I myself do meticulous work. But it's also important to get the process moving. One example is better than a thousand pages of manuals, IMHO. And nothing is written in stone. People can learn from their mistakes and modify thngs.
And, perhaps I'm wrong, but I think the poster is at a very basic level. Talking about visula basic and such seems way ahead of the game. It's not even clear if the poster really even needs a form. They may just need a result i.e. just run the query once they get it put togther in a suitable fashion.
Anyway, we may just have to agree to disagree about technique and such. No big deal. I'm sure things are in good hands with Jasper.
posted by bim at 7:02 PM on March 4, 2006
This thread is closed to new comments.
You can only make that output recordset if you have a relationship between Customers and Reports or Projects.
I suspect what you need to do is go read a big tutorial on database design, normalisation and the like. Once you've got a better handle on the design and table relationships, then go look up an Access tutorial.
posted by polyglot at 12:53 AM on March 4, 2006