Access form set up

[Jwil]

Hi,

I've used excel for years but I'm completely new to Access (I've done the
online training modules and that's about it) and have what probably is a
simple question. How do I create a from that looks like a table or a
spreadsheet? So for instance it has 10 or more rows and 4 columns. I would
like to hide the labels so that it looks like a spreadsheet as well. I
assume it would attach a unique number to each cell like excel does A1, B1,
and so on. I would think that it could do this automatically.

 

[John W. Vinson]

Hi,

I've used excel for years but I'm completely new to Access (I've done the
online training modules and that's about it) and have what probably is a
simple question. How do I create a from that looks like a table or a
spreadsheet? So for instance it has 10 or more rows and 4 columns. I would
like to hide the labels so that it looks like a spreadsheet as well. I
assume it would attach a unique number to each cell like excel does A1, B1,
and so on. I would think that it could do this automatically.

Ummmm... No.

Access is not a failed implementation of Excel. It's not even "Excel on
steroids". It is a DIFFERENT program, with different structures, different
concepts, and a different mindset required to work with it.

You can create a form in Datasheet view which will look like a spreadsheet...
but it isn't a spreadsheet, doesn't allow calculations in cells, doesn't have
row numbers, and does have fieldnames rather than column letters (you could of
course use A, B, C as the fieldnames or as the field captions to *display*
those letters).

But do try to work *with* Access as it is designed, rather than creating a
Procrustes bed to force it into the shape of Excel, or you'll end up very
frustrated indeed!

 

[Steve]

Hello,

Frankly, you are on the wrong path as John hinted at. I provide help with
Access, Excel and Word applications for a small fee. I would like to offer
to help you with your first database. My fee would be very modest. At
completion you would have a database from which you could model any future
database you need. Contact me and let's work together on your database.

Steve
(e-mail address removed)

 

[Jwil]

Ummmm... No.

Access is not a failed implementation of Excel. It's not even "Excel on
steroids". It is a DIFFERENT program, with different structures, different
concepts, and a different mindset required to work with it.

You can create a form in Datasheet view which will look like a spreadsheet...
but it isn't a spreadsheet, doesn't allow calculations in cells, doesn't have
row numbers, and does have fieldnames rather than column letters (you could of
course use A, B, C as the fieldnames or as the field captions to *display*
those letters).

But do try to work *with* Access as it is designed, rather than creating a
Procrustes bed to force it into the shape of Excel, or you'll end up very
frustrated indeed!

Thanks for the reply. I would've thought that arranging text boxes to look
like a table would be fairly easy actually. The tables in Access are exactly
how I want part of the form to look. The tabular/stacked commands do this
somewhat but not in the dimension I want. i.e. 4x10. Each field is already
unique so the numbering or naming shouldn't be a problem either. I've
already done it manually but it would have been nice for it to do it
automatically. I'm actually going from Filemaker to Access and wanting the
form to look like it did in filemaker.

 

[Jwil]

Hello,

Frankly, you are on the wrong path as John hinted at. I provide help with
Access, Excel and Word applications for a small fee. I would like to offer
to help you with your first database. My fee would be very modest. At
completion you would have a database from which you could model any future
database you need. Contact me and let's work together on your database.

Steve
(e-mail address removed)





.

Hi Steve,

Thanks for the offer. I'm moving along pretty ok so far and there is a lot
of free tutorials and videos out there that will help me do exactly what I
need. (which isn't much) I'll shoot you an email if I run into any problems.

Thanks

 

[John W. Vinson]

Thanks for the reply. I would've thought that arranging text boxes to look
like a table would be fairly easy actually. The tables in Access are exactly
how I want part of the form to look.

Well, have you investigated using a Datasheet?

 

[John W. Vinson]

Hi Steve,

Thanks for the offer. I'm moving along pretty ok so far and there is a lot
of free tutorials and videos out there that will help me do exactly what I
need. (which isn't much) I'll shoot you an email if I run into any problems.

Thanks

 

[John W. Vinson]

Thanks for the offer. I'm moving along pretty ok so far and there is a lot
of free tutorials and videos out there that will help me do exactly what I
need. (which isn't much) I'll shoot you an email if I run into any problems.

Thanks

You might want to check out

http://home.tiscali.nl/arracom/whoissteve.html

Steve is notorious for grubbing for work in these newsgroups... unlike the
vast majority of the other responders, who may earn money from databases but
who respect the rules and customs of the newsgroups.

 

[David W. Fenton]

I'm actually going from Filemaker to Access and wanting the
form to look like it did in filemaker.

Access is not Filemaker. If you don't want to learn Access, then go
back to Filemaker.

The fact that Filemaker implements a presentation type that mimics a
spreadsheet is probably not helpful in the long run in regard to
learning how to deal with a database instead of a spreadsheet.

Of course, I'm assuming that you have made a correct decision in
moving to Access from Excel. Perhaps if you explained your reasons
for doing that, we could explain why it's a good/bad idea and guide
you in making the switch (or recommend going back to Excel!).

 

[Jwil]

Access is not Filemaker. If you don't want to learn Access, then go
back to Filemaker.

The fact that Filemaker implements a presentation type that mimics a
spreadsheet is probably not helpful in the long run in regard to
learning how to deal with a database instead of a spreadsheet.

Of course, I'm assuming that you have made a correct decision in
moving to Access from Excel. Perhaps if you explained your reasons
for doing that, we could explain why it's a good/bad idea and guide
you in making the switch (or recommend going back to Excel!).

Wow, unlike the Excel forum which I've participated in for years, the
assumptions in this forum just keep coming lol! I know Access is different
from Excel, I know Access is not filemaker, I never said I didn't want to
learn Access which by the way is the reason I'm even on this forum and have
gone through the online training videos., I'm not moving from Excel to
Access, I'm going from Filemaker to Access for many circumstances out of my
control but frankly that had nothing to do with the question.

Please don't take this as ungratefulness. I realize that these forums are
inundated with people who don't even look at the help topics and people who
don't know the difference between programs. In the end I know that you're
trying to help but that whole "if you don't want to learn Access" comment
wasn't called for.

So back to my question, if you're still interested , So I've manually made
a form and arranged my fields to look exactly like my filemaker form but I've
been reading in the forums and i see that you shouldn't have more than 30
fields?!?! As you can image, if this looks like a small spreadsheet (4X45) I
have 180 fields. I had to merge a 2009 database as is but I have the liberty
to change the 2010 how I want. How would I go about it differently while
keeping the same look? If you give me your email I could send you a picture
of what the form looks like. It's a very simple looking form but the records
get in the thousands.

Thanks you.

 

[John W. Vinson]

So back to my question, if you're still interested , So I've manually made
a form and arranged my fields to look exactly like my filemaker form but I've
been reading in the forums and i see that you shouldn't have more than 30
fields?!?! As you can image, if this looks like a small spreadsheet (4X45) I
have 180 fields. I had to merge a 2009 database as is but I have the liberty
to change the 2010 how I want. How would I go about it differently while
keeping the same look? If you give me your email I could send you a picture
of what the form looks like. It's a very simple looking form but the records
get in the thousands.

The 180 fields is a) pushing awfully close to the rock-hard 255 field limit
and b) is quite certainly non-normalized. FMP is a good application, but it
does *NOT* encourage design as well normalized as it might be!

This isn't a software limitation so much as it is a logical one. The
relational data model works best with normalized tables, and normalized tables
do not have repeating fields. In addition, if you are storing data (a date) in
the *name of a database* you're on the wrong track! Dates are *data* and
should be stored, as dates, in a field in a table; a Query can easily extract
just the data for 2008, or 2009, or 2010.

I really get the feeling that you're confounding data *PRESENTATION* - the
appearance of your spreadsheet - with data *STORAGE*. These are different
tasks! Sure, you can create a continuous form with 180 fields (the user will
need to scroll a lot); but that doesn't mean that the table should be
structured that way.

Could you post - in text - a partial example of your fieldnames and their
significance? Dollars to donuts, you are embedding a one to many relationship
(perhaps more than one such relationship) in *each record* in your table.

 

[Jwil]

Could you post - in text - a partial example of your fieldnames and their
significance? Dollars to donuts, you are embedding a one to many relationship
(perhaps more than one such relationship) in *each record* in your table.

Sure, I'll try my best to explain.
It's a cost estimate database. Each job that comes in gets logged in with a
estimate of how much it will cost. (This also happens to fit nicely onto one
page.)
So there are tasks that need to be performed on each job. currently we can
only reasonably get 40 tasks on a page so we just stopped there. One job may
only need 3 tasks performed, others maybe need 30. But all 40 lines are
available to be filled out. Each task has to be broken out into 4 columns of
data. These columns are quantity and 3 different costs. Then at the bottom
everything is totaled a specific way.

So the "Records" are the jobs that come in. This is also my primary key.
There's other info like your name, customer name, date, etc. that is also
stored for each record.

So my field names are task 1 - thru - task 40, cost 1 - thru cost 40, etc.
plus all the other basic stuff like date and name and other info. So as it is
now, 1 record needs all those fields. It was a nightmare to set up (probably
because I'm a newbie) but now that I already have it set up the form just
looks like the regular piece of paper that everyone is used to. It was just a
cosmetic issue for the merge.

So I'm basically just trying to do it right for the 2010 database so that's
why i was asking if there was a better way to go. I'm also enthralled by the
vast amount of capabilities that Access has to offer but I'm sure it will be
harder to implement them if you don't even start off on the right track lol.

Hopefully this was clear and thanks a lot for the hekp.

 

[John W. Vinson]

Sure, I'll try my best to explain.
It's a cost estimate database. Each job that comes in gets logged in with a
estimate of how much it will cost. (This also happens to fit nicely onto one
page.)

Stop right there.

The design of your tables should NOT be influenced AT ALL by the size of a
page or by data presentation.

The design of your tables should be completely controlled by the logical
relationships of the Entities - real-life persons, things, or events - that
the database represents. Create a logical, normalized relationship between the
tables, and *then* start working on ways to present that information to the
user!

So there are tasks that need to be performed on each job. currently we can
only reasonably get 40 tasks on a page so we just stopped there.

Could you accept a design which would allow *any desired number* of tasks for
a job - one job might have three, another thirty, another (building a venue
for the Winter Olympics say) three thousand? Perfectly straightforward with
proper relational design.

One job may
only need 3 tasks performed, others maybe need 30. But all 40 lines are
available to be filled out. Each task has to be broken out into 4 columns of
data. These columns are quantity and 3 different costs. Then at the bottom
everything is totaled a specific way.

Again... you're confusing data STORAGE with data PRESENTATION.

So the "Records" are the jobs that come in. This is also my primary key.
There's other info like your name, customer name, date, etc. that is also
stored for each record.

So my field names are task 1 - thru - task 40, cost 1 - thru cost 40, etc.
plus all the other basic stuff like date and name and other info. So as it is
now, 1 record needs all those fields. It was a nightmare to set up (probably
because I'm a newbie) but now that I already have it set up the form just
looks like the regular piece of paper that everyone is used to. It was just a
cosmetic issue for the merge.

The proper design for this involves *several tables*, none with as many as ten
fields:

Clients
ClientID <primary key>
LastName
FirstName
<other info about the client/customer>

Projects
ProjectID <primary key>
Description
ClientID <who is this project for>
StartDate
<other fields about the project itself>

Tasks
TaskID <primary key>
TaskDescription <e.g. "grade and level site">
<any other info about the task itself independent of any project>

ProjectTasks
ProjectID <link to Projects>
TaskID <link to Tasks, this task is needed for this project>
TaskCost
<info pertaining to THIS task for THIS project, e.g. date started, date
completed, ID of the person or team doing the task, etc.>


"Fields are expensive, records are cheap". If a project has many tasks, you
would enter many records into the ProjectTasks table, one for each task, each
with its cost; a Totals query (or a total on a Form or Report) can sum the
costs. *RECORDS*, not fields!

So I'm basically just trying to do it right for the 2010 database so that's
why i was asking if there was a better way to go. I'm also enthralled by the
vast amount of capabilities that Access has to offer but I'm sure it will be
harder to implement them if you don't even start off on the right track lol.

Hopefully this was clear and thanks a lot for the hekp.

Just try not to get hung up on modeling your database on a paper form. The
project isn't a paper form! The paper form is a good tool to manage the
information about your project - *if the only tool you have is a sheet of
paper*. You're not stuck with just using a sheet of paper; you have a powerful
computer and a powerful logical framework ("Normalized Database Design") that
is much more capable and much more flexible than an 8.5 x 11 inch sheet of
paper!

 

[Jwil]

Stop right there.

The design of your tables should NOT be influenced AT ALL by the size of a
page or by data presentation.

The design of your tables should be completely controlled by the logical
relationships of the Entities - real-life persons, things, or events - that
the database represents. Create a logical, normalized relationship between the
tables, and *then* start working on ways to present that information to the
user!


Could you accept a design which would allow *any desired number* of tasks for
a job - one job might have three, another thirty, another (building a venue
for the Winter Olympics say) three thousand? Perfectly straightforward with
proper relational design.



Again... you're confusing data STORAGE with data PRESENTATION.

Sounds good. That's the path I started to take so I'm glad I'm on the right
track.
I'm still confused a little though but I'll keep at it. If I have a project
of veryfing that a transistor is good. 1 project (the transistor) has 50
tasks, most of which are created specifically for the transistor. Storing the
data is the easy part. I can have a client table, an engineer table, a task
table, a project table, and whatever else I need. It's the bringing it all
together I have problems with. I need to make a form that has the one record
of the project with the 50 records of the tasks. That is the critical piece
that i'm missing.
Just to clear up I was never trying to fit a table on a sheet of paper.
that would be CRAZY lol. It was the form that I need to fit on one or many
sheets of paper. The form is the ONLY presentation element in this whole
project. Judging by all the templates and what I've read that seems like the
intent of the form but I maybe wrong. Like I said before I'm completely new
to it and the help you've given has been great.

Thanks.

 

[David W. Fenton]

I need to make a form that has the one record
of the project with the 50 records of the tasks. That is the
critical piece that i'm missing.

Main form for the project, subform for the tasks attached to that
project. This is one of the most basic concepts in Access. I wonder
if someone could chime in with some good tutorial recommendations,
as I've been using Access for so long that these things are just
second-nature to me.

Form/Subform is not by any means unique to Access -- it's the way I
built forms back in my Paradox for DOS days (1989-95), and every
other database application front end I've seen uses the same concept
(though not always as cleanly and efficiently implemented as is the
case in Access).

 

[John W. Vinson]

I'm still confused a little though but I'll keep at it. If I have a project
of veryfing that a transistor is good. 1 project (the transistor) has 50
tasks, most of which are created specifically for the transistor. Storing the
data is the easy part. I can have a client table, an engineer table, a task
table, a project table, and whatever else I need. It's the bringing it all
together I have problems with. I need to make a form that has the one record
of the project with the 50 records of the tasks. That is the critical piece
that i'm missing.

The most common - and IMO most user friendly - way to do this is with a Form
(for the transistor) with a Subform (for the tasks). A combo box on the
subform will let you pick which test is being applied, and you would have one
or more textboxes or other controls to enter the results of that test; or if
all you care about is whether or not the test was done, just selecting from
the combo will do that.

 

[John... Visio MVP]

Frankly, you are on the wrong path as John hinted at. I provide help with
Access, Excel and Word applications for a small fee. I would like to offer
to help you with your first database. My fee would be very immodest. At
completion you would not have a database from which you could model any
future database you need. Contact me and let me con you out of your money.
I need to harrass posters because I never get any repeat customers.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[John... Visio MVP]

Well, have you investigated using a Datasheet?

John W. Vinson [MVP]

You mean other than PC Datasheeet?

John... Visio MVP

 

[Jwil]

The most common - and IMO most user friendly - way to do this is with a Form
(for the transistor) with a Subform (for the tasks). A combo box on the
subform will let you pick which test is being applied, and you would have one
or more textboxes or other controls to enter the results of that test; or if
all you care about is whether or not the test was done, just selecting from
the combo will do that.

Hi John,

So I'm just reading this but I sort of did just that. Last week, after I
made my relationships, When I created my form it inserted my tasks table
right there which was perfect! So that's exactly what I wanted. I have one
final question and then I think I'll be done. I do want to sum the results of
the 3 cost fields. I've been reading the forums and also based on what you
wrote above I know I can do this using a form and a subform. Can I do this
however with the Form and the table that was inserted? So I have a form
created from my table "2010 Test Information" and in the form was inserted my
task table, "2010 Test Descriptions" I'm thinking no but it doesn't hurt to
ask.

Thanks.

 

[John W. Vinson]

Hi John,

So I'm just reading this but I sort of did just that. Last week, after I
made my relationships, When I created my form it inserted my tasks table
right there which was perfect! So that's exactly what I wanted. I have one
final question and then I think I'll be done. I do want to sum the results of
the 3 cost fields. I've been reading the forums and also based on what you
wrote above I know I can do this using a form and a subform. Can I do this
however with the Form and the table that was inserted? So I have a form
created from my table "2010 Test Information" and in the form was inserted my
task table, "2010 Test Descriptions" I'm thinking no but it doesn't hurt to
ask.

Thanks.

You don't need anything very complicated to sum the costs. However, you say
you've normalized the tables... but I don't know your current table structure.
What tables do you have? Where are these costs stored: as multiple records in
a table (probably a good idea), or as multiple fields in a record (possibly ok
but probably not ideal)? What are the Recordsources of your form and subform,
and what is the subform's Master/Child Link Field? Just what do you want to
sum? You say "the three cost fields" but I don't know what those are.