excel to access

[mdcory]

Hi all. Let me introduce myself, my name is Matt and I am fairly new to access. I am an operator at a wastewater plant in Iowa (insert ewww's here). Since I am the most computer literate at work, and have some experience at access, they gave me the task of creating a database for our lab. Currently they use excel to record all of the data for all the test run everyday with a seperate file for each test each day. That makes it very hard to generate a report at the end of the month to send to the state DNR.
I have been reading some of the post here the last few days and have gotten some great tips and ideas, especially in the UI of how I want the finished product to look. You guys are great and have been a lot of help to me already.

My first question for you experts is this: Is there a way to take the excel files and import them to access as a form so I have the general layout of the form instead of starting from scratch? I have over 57 tests that I have to create seperate forms for. It would make my life easier if there was a way.

Thanks in advance for the help, and I'm sure, if you don't mind, I will be back with a few more questions as I go along.

Thanks again,
Matt

 

[Pat Hartman]

You need a little more study before you jump into this. What you don't want to do is to reproduce your spreadsheets as tables. You will be very unhappy with Access if your tables look just like the original spreadsheets. Do some reading on database design and normalization.

The data from a spreadsheet table (a set of row and column oriented cells) can easily be imported but you'll need to create new forms in Access. The wizards can help you.

 

[mdcory]

Thanks for the reply, I already have my tables set up, I ended up with 3 tables, one for metals one for wet test and one for organics. I spent a week going through all of the test we do figuring out what results and such I would need as fields and so on. I included a partial spreadsheet of what I am talking about. I also included an excel file of what one of the finished form will kind of look like. I will have that form for the daily data entry and have seperate reports for seeing the months data at once for the different test and so on. After I posted my message I thought more about and realized it would be more hassle to import anyway if it was possible.
Thanks,
Matt

 

[Pat Hartman]

You didn't post the tables. Do you have a question?

 

[PierreR]

MD.

You don't need a different form for each of the 57 tests if you set up the database properly. You're probably going to need only 1.

What you want are 57 different report printouts, one for every test but don't fear, it is still only 1 report ...

You should base the report on a crosstab query. It should be set up to limit its output to any one date, which will give you any day's single test. Please tell me if I've misunderstood your description.

As for your original question. NO, there is no way to import what you think of as a spreadsheet form into Access. To Access it is a spreadsheet, not a form.

Pierre.

 

[PierreR]

See this amazing thread about doing it right your own way. Mike who is being attacked there for his lack of normalization and refusal to use VBA, has an insurance company in Australia, and is using multiple same-type of tables - maybe you can write him directly. I'm sure he'll want to help you. Maybe he really has a point?

Mike's do it his own way thread.

I believe you are not going to use the unique power of Access if you don't set it up the way it was designed to be used. The way you seem to be going now is probably going to be completely ineffective.

Yes, you will have your reports, but it's going to take a lot of work.

 

[mdcory]

Thanks for the replies. I don't think I have explained this well enough. There is no possible way that I can come up with that would allow me to have just one form. Each test has it's own formulas that use different standards and other data that will be provided on the forms, there is an example in the zip attached earlier.
There will be a lot of calculations on each form, some are pretty complex such as slope, fit, intercept. One other question I have is this: I have read that it is not a good idea to store a calculated value in a table. What are the reasons for that? It would work better for me to store the value in the table so I can print a report with the calculated value instead of having to get all the raw data have it calculate again. Would I be totally wrong storing that value?
Thanks,
Matt

 

[PierreR]

I'm going to work on an example and send it to you. I wish it were Friday afternoon, so I'd have more time, because tomorrow morning you're going to have to work on the database in the wrong direction again. I'm not versed in the official reason why they say that it's wrong not to store calculated values in a table, but if Excel recalculates every time you open it, (and it does), then they must be right about Access too?

This is what I think: If you have a form with formulas in it or underlying it in a query, the values are recalculated anyway, and would overwrite the values previously stored in the tables.

Access recalculates because recalculation enables the user to seamlessly change either/or the variables and parameters. That is in step with the central philosophy of a relational database; to enter a single piece of information or change that information in only one location, which then propagates throughout the application.

On my own learning curve, I'm starting to see this with VBA programming too. A similar philosophy is at work . If you can code only once for a widely used function, do so, instead of repeating the same coding over and over again at the level of forms or reports. Then refer to that code by name in the forms and reports.

 

[PierreR]

So far it is difficult because I can't easily conceptualize your process, but I'll try a little bit and let you know either way.

 

[PierreR]

Matt,

I have an example of how I would do it in the zip. Just to give you an idea. If you want to, we can correspond and I can send more developed versions to you by e-mail as we go (I don't want any money, because I see it as preparation for when I start asking money within a week or two, so this is not advertising)

It is worth the try, isn't it? It's free, and I know if you use it, it is going to save you lots of aggravation compared to your current plans. My own benefit is the hands-on experience.

I just want to add that the tables now have only the basic fields. If we do this we can decide what fields go where. If you don't want to do it, I'm going to guess the best I can, and send you a rough working version within a day or two.

Pierre.

 

[mdcory]

Sorry, really busy day, I will look at it tonight and post more tomorrow. Thanks again...

 

[mdcory]

Thanks Pierre, that just seems more complicated then it needs to be. But maybe it's just me. I'll attach the db that I have so far. I have the metals test table and a form for Iron. The form is a rough version, I don't have all the calculations on it and just some fake data pluged in. Take a look at it maybe it will help clairify somethings. Thanks again for your help.