New to Access - real basic help needed

[June7]

I never use the form and report wizards. If you based the form on a query that includes all these related tables then the wizard probably created a single form and this is not an editable dataset, especially if there are multiple 1-to-many or many-to-many relationships.



I suggest you need a better understanding of relational database principles, Access functionality, and programming concepts. Consider an introductory tutorial book or some web-based tutorials.
http://office.microsoft.com/en-us/ac...010098674.aspx
http://www.rogersaccesslibrary.com/

[ItsMe]

... I was under the impression that it would only work if I had correctly related all of my tables...

It is possible. If it works in your query, it will work in the form. Test the query. Base the form on the query. Use "Design View" to build and design your Forms. Use the "Record Source" property within the property sheet of the form to select your query and associate it to the form.

If multiple joins are not allowing you to edit the fields and or add new records. Adjust the query, then use ComboBoxes and or other methods to update the fields not included (because you had to remove them) in the original query.

Sometimes, you can update a field within a query but cannot update another field within the same query. Some fields are updatable while others are not.

If you like, you can upload a sample of what you have tried so far. Remove private data, zip down the DB and upload here (use the "Go Advanced" option here in the thread)

[tc197]

Thanks for all the help guys. Sorry I couldn't reply sooner but I don't have access to this stuff from home. I attached what I have done so far and I think I did what June7 said and the wizard created 1 form for me that I can't edit.

EDIT:

I also only have 1 query and that's the one that just makes the fields from the different tables update to whatever is in my first tables field automatically. Am I supposed to have more?

[tc197]

I'm making lots of progress using subforms. I can't seem to get it to format how I like it but maybe someone can help me out with that in a little bit? I have scroll bars and borders that won't go away no matter what properties I change.

[tc197]

Update-

I was successfully able to create my form using sub forms and it works almost exactly as I need it too. I have a few more formatting questions like how to get rid of some weird lines and how to make the forms so that they don't roll back into previous records when I use the scroll wheel on my mouse. My next step is that I want to be able to search through my tables and generate a report based on what I searched. Hopefully this is possible but I have yet to even touch the report button so I'll be back with question on that for sure. If anyone knows how I can get rid of those lines and such that would be awesome. Here's the latest copy of what I have!

EDIT:

Also is there a way to add a button that works like an enter button? After someone fills out all the information, they hit enter, the data is recorded and all of the fields become empty again?

[ItsMe]

I have a phone conference in a couple of hours and need to prepare for it. However, I will take a look at the file you have in post #20 and get back to you ASAP

[tc197]

I have a phone conference in a couple of hours and need to prepare for it. However, I will take a look at the file you have in post #20 and get back to you ASAP

That would be awesome, thank you very much and theres no rush. Things I'm stuck on:

1. Preventing the scroll wheel from going through records - I want the scroll wheel to scroll the page up and down like it usually does.

2. Hiding the lines on the left side of my subforums and if possible the record information at the bottom of each one

3. Creating copies of my form "Genera info" so that I can edit the copies individually. Kind of like save as with a word document. I need 4 variations of this same form so I wanted to copy and paste the form but when I edit the copy it also changes the main.

4. Figuring out how to write a search query where the user types in the "unit number" and a report is generated that contains all of the information related to said unit number. (I'm just starting to look into this one now)

[ItsMe]

I do not see any reference to dogs. The analogy may still be beneficial but your testing is more complex than a simple evaluation of how happy a specific pet is on a given day.

I will guess that possible measurements of resistance are infinite. You should use a field that is of type Number and Double to measure resistance. You should not need a table for this. You are "committing to spreadsheet" by building multiple columns. Having a column in a table dedicated to a specific measurement is not Normalized (refer to RDBMS tutorials). If it is true that possible measurements of resistance are infinite, you will need an infinite number of columns in your table to store the results.

You are continuing to "commit to spreadsheet" when you create fields for anode and fields for cathode. Having a field/column in a table that is specific to a "Anode measurement of resistance" and another field for Cathode measurement of resistance is not a good idea. Now you need an infinite number of columns for your table times two. Infinite times two is bad.

I do not understand your Business Rules. I will offer advice to work down, in Rows. Do not work across, in columns. Do not Commit your table to predefined columns when those columns need to be dynamic (grow, change, shrink).

In your events table, provide a field to store resistance. Make it Number type / Double. Have the user type a value of resistance. Create another field/column. Make this Yes/No type. Have the user enter a value into this field to indicate Yes Anode or No Cathode. Now your query can sort out the details for reports.

This is a small part but, hopefully I understand what you are trying to accomplish and this puts you back on track. Understand RDBMS.

[tc197]

Sorry for the confusion. I'll try to clear up what I was assigned to do. Currently we have an excel spreadsheet that is used to record all of the information shown on my general_info form. I was asked to turn this excel spreadsheet into an access database but to make it look as close as possible to the excel sheet.(The people who fill in the form don't want to have to learn some new program/ fill out a different form than usual.) The form that I designed looks the exact same as the excel spreadsheet but instead it records the data into separate tables that can be linked using the unit_number. No new columns are going to be added to any tables, only the data recordings will be added row by row for each unit number. So, for unit #123, it will have a resistance for each cell from 1-48, an interconnect for 1-48, cement 1-48, the various voltage measurements, and the various anode-cathode measurements etc. Then we move onto Unit #1234 and the same measurements are taken and recorded.

I'm not quite understanding where the infinite you mentioned comes into play because there are 48 cells, and there will be 48 measurements of resistance, thus there are 48 columns. Each row represent a new unit and thus a new set of measurements. The same applies to the rest of the tables. The end goal is that I can write a query where it prompts a user to search for a unit number, each row in each table that has that exact unit number will then be added to a report and it will essentially re-populate the form in the same manner the data was entered however long ago.

I'm not quite sure why my columns need to be dynamic as well, I would say my rows need to be dynamic because they will grow every time a new unit is manufactured and tested.

Maybe I'm just completely off but I appreciate you taking the time to look at what I have and I'll try to learn more about RDBMS and hopefully I've been able to clear some things up.

[June7]

I agree with ItsMe - your data structure is not normalized and will likely cause you issues.

Right now you have 48 cells (a limited number of measurements, not an infinite/unknown number). What if the requirement changed to 60 cells? This would mean modifying tables, queries, forms, reports.

Also, do you need to do statistical analysis of the 48 cells - average, standard deviation, etc? Do you want to graph the data? This will be very difficult with this structure.


1. AFAIK, scroll bar always scrolls records, not 'pages'

2. set form properties

3. edits on one form will not carry over to 'copies', they are independent, suggest one form and use code to manage the 4 variations

4. several methods to accomplish
dynamic parameterized query http://www.datapigtechnologies.com/f...tomfilter.html
code (macro or VBA) to set form Filter property

[tc197]

OK now I understand what you mean by changing, and my plan was that I would just create 5 forms for the 5 different units we have (48 cell, 36, 30, 20, 10) all based off of the 48 cell form. The only change is I would copy and paste the 48 cell form and delete cells 37-48 and then rename it as the 36 cell form. Part two to all of this is that I am an intern and this is week 4 out of 10 for me at the company. I have no idea what is going to be done with this data in terms of analysis and I was told they want a way to search through the records and pull up information that they need. I don't really understand how this data could be structured differently, is there some sort of example because in my head the only way I can figure to keep measurements for 48 cells straight is to have 48 columns. The company process as a whole takes a long time to change and if for some reason they decided to do a 60 cell unit it would be months if not years out and then it would be more years until the next big change. I figured if it needed to be modified to fit something different it wouldn't take much effort and wouldn't be happening every week. I really appreciate this help though guys, its making my life a lot easier because you help me get past obstacles so I'm not sitting here twiddling my thumbs as to what to do.

[tc197]

I agree with ItsMe - your data structure is not normalized and will likely cause you issues.

Right now you have 48 cells (a limited number of measurements, not an infinite/unknown number). What if the requirement changed to 60 cells? This would mean modifying tables, queries, forms, reports.

Also, do you need to do statistical analysis of the 48 cells - average, standard deviation, etc? Do you want to graph the data? This will be very difficult with this structure.


1. AFAIK, scroll bar always scrolls records, not 'pages'

2. set form properties

3. edits on one form will not carry over to 'copies', they are independent, suggest one form and use code to manage the 4 variations

4. several methods to accomplish
dynamic parameterized query http://www.datapigtechnologies.com/f...tomfilter.html
code (macro or VBA) to set form Filter property

1. Is there a way to set the scroll wheel to do nothing in access then? I don't care if it scrolls pages or not but right now what happens is I'm typing and if I bump the wheel it cycles through different records and it just seems like a problem waiting to happen.

2. What form properties do I change? I already set everything to flat and transparent and I can't find anything else related to appearence. Does the little record display at the bottom of the forms have a name?

3. Solved this one, thanks!

4. I'll watch that now.

[ItsMe]

For Access 2003 you need to use some fancy footwork to disable the Mouse Wheel. I can help you out with that. For now concentrate on RDBMS and try to ask questions about Normalization. For now, don't worry about the Mouse Wheel cycling the records.

As for the Infinity thing. It really was a poor choice of words. In fact, you can quantify all of the possible values a type Number field can store, so not infinite. But I was off point about how your organization is evaluating a product. It seems they only run a predefined number of tests. But, as June pointed out, the bottom line is your tables are not Normalized. So back to RDBMS. Like June mentioned in her earlier post, need to understand the basics.

I believe what is throwing you off is the Excel spreadsheet and the boss saying "Do it this way".

Reverse engineering a spreadsheet to normalize and store its data in an RDBMS is one of the more difficult things an engineer can do when they attempt to architect a Relational Database. Add to that the Business Rule that your form's aesthetics must mimic existing spreadsheets and you are bordering ridiculous. You will never understand RDBMS approaching it from a spreadsheet perspective. Furthermore, Form objects have little to do with Relational Databases if anything at all. Forms are GUI's to interface with a Relational Database. Form's are part of an application. Applications (as a whole) are not a relational database. Use one side of your brain to build the Relational Database and normalize your data. Use the other side of your brain to build the application. Call on your artistic abilities to develop apps. Don't be creative when developing Relational Databases.

[June7]

1. I don't know, never been an issue for me.

2. consider form properties on Format tab:
Navigation Buttons
Record Selectors

If they insist, build what they ask for but they should be aware of the difficulties they could face with data analysis. The search you mention - would that be to search for unit number? That can be handled easily. But if they wanted to find all measurements for all cells that met specific criteria, that would mean applying the same criteria to 48 fields with OR operator and that means 48 criteria rows in the query.

Normalized structure would mean a table for the unique Unit IDs and a related table for the cell measurements. Making the data entry form of a normalized db look like the spreadsheet would not be easy and possibly involved UNBOUND form and lots of VBA code.

Maybe this site will help http://www.rogersaccesslibrary.com/

[tc197]

Thanks again for taking the time to help me, I really have no choice but to do what they ask for and that definitely hasn't been helping me out but I feel very confident in what I have so far. I solved the search query issue, my form works, the data pops up as I would like it to, and I can successfully generate a report of any unit with all the measurements using the unit number. All I have left to do is some fine tuning and formatting stuff which I would love to continue to ask questions about.

One question I had is, is it possible to search twice to display data. For example, each unit has a number and each unit has 48 cells. Each of the 48 cells also has a number. Is it possible to write a query that will search for an individual cell number, find the unit number associated with the cell number, and then display all of the data according to that unit number?

Right now I can search for unit numbers and display everything related to that unit number but I was wondering if it could work indirectly too.

I would love some help getting rid of that mouse wheel later on-- it doesn't bother me so much but I keep bringing what I have over to the people who will actually be using this form and they hate that when they touch it the records move around.

Also for any sort of analysis i think they may pull individual data and just use excel to run those numbers. Not too sure though