Oct 20 2004, 09:23 AM
not even sure of what to search for, so if this is a duplicate post, forgive me.
i have a report that requires to have 97 fields, because of one entry.
the other entries only require 2-30 fields. the problem i have is space. on my form i have 97 spots open and visible and taking up two pages. no big deal. its just an easier way to input the data.
what i am lookin for is a way to make those fields that are blank vanish on the report and have the next entry come up and take its place. is this possible to do? if so, please break it down barney style. i am not tech savvy with access and my algebraic skills ... well lets just say i passed my college algebra with trig class with a C (thank goodness for curves).
thanks in advance
Oct 20 2004, 07:47 PM
You may have an underlying design problem as properly designed queries/reports can usually pull out data without all the blanks. You might zip you db and post it so that we can take a peek.
Oct 21 2004, 06:52 AM
underlying problem? not sure what you mean by that.
basically when i hit report, after just setting up on the column wizard, place the boxes i need, where i need them, and i look it the report. i see all the boxes, empty or not.
what i want, is to make all of those boxes (empty entries) disappear. and the following entry set to automatically come up under the last actual entry.
is this possible? and again, Barney style for me. if you want you can make a little song out of it to help teach me heh.
i cannot send what i am working on.
Oct 26 2004, 11:55 AM
Sorry for the delay getting back to you, I've been off a few days. I mean your database may not be designed properly so it isn't working as it should. You still may be able to get a report of sorts but the design will not allow you to get all you might be able to or in the way you would like. The 97 open slots is an example, a proper design should be able to avoid this problem. I'd suggest you copy your db (database) zip it (compress it via a zip program, if you don't have one do an internet search on "Free Zip" to download a program to let you do this) and attach a zipped copy of your db to your reply to this message for me or somone else here to look at and make suggestions.
Oct 26 2004, 12:07 PM
I agree with Chris. If you have a structure where one record requires 97 fields and others only require 30 fields, then it is very possible you have design flaws in your database. If we fix the underlying disgn flaws, the report issue may take care of itself.
You can create a copy of your database with dummy data if the data is confidential. If you can't send a copy, then you need to describe, greater detail, the purpose and structure of your tables. Once we know more about the strucutre we can help further.
Oct 26 2004, 12:34 PM
not sure if i understand where you guys are coming from. flaw with my dbase? it is about as basic as you can get. afterall, what do you expect from a guy who self taught himself access heh.
ok, again, let me try to clear things up.
i have a location, there can be any number of things in this location. i have to record these things.
one location has 97 "things". in the form version, that is not a problem. but to print out a document in report form, which looks like the form version, i have 2 pages per location. i have 2,000 locations. so printed out i am looking at 4,000 sheets of paper. this isn't acceptable.
the reason why i will have 2 pages per location is because all of the blank "entries" for the things remain, even in the report format.
if i can get those empty blocks on the report form to disappear when not used, that would be awesome.
now, i created my report in columns, to make things as simplistic looking as possible, in 2 columns and 97 entries total.
if i need to create a whole new report, let me know. perhaps there is an easier way to do this. but 2 contants remain, i need to group to the location, and i have 97 things to group with that location.
i had made 3 tables, one with a building number, another with a room number, and another with a list of things.
(dont get too wrapped up in the building, room and things,, they are dummy).
my goal is to get these things meshed together into a nice and neat report that only displays the information as its used. is this possible for access, or am i looking at more advanced stuff?
Oct 26 2004, 12:54 PM
I think you need to review some of the posts here on normalization. Clearly your database is not normalized and that is causing your problem.
Oddly, you describe a structure that is more they way it should be and then tell us its a dummy.
You should have at least 2 tables. One table to describe the location, the other table for the list of "things". You probably should have a third table that is a lookup of the potential "things".
From there you create a mainform/subform with the mainform bound to your locations table and the subform bound to the "things" table and linked to the mainform on LocationID. You bring up each location in the mainform and add a record in the subform for each thing in that location. So one location may have 2 things another 97. But there will only be a record for a thing if it exists in the location.
Now you create a query that joins the locations table and the things table and create your report based on that query. You set the report to Group by location and set the Detail band to list the things. You can set up the report for multiple columns if you wish.
This report will show a list of only those things in each location like you want.
This is really a prime example of how proper design makes reporting (and data entry) easier.
Hope this helps,
Oct 26 2004, 02:39 PM
wow! let me take a look at that again tomorrow and try to digest that. that was some pretty deep stuff.
queries and such i am still learning,, its a little complicated to do anything than a simple querry. but i am learning. now when you say to add a record in subform, how would i do that if i had 50+ items to input? would i need to create some sort of relationship? or am i making this even more complicated?
thanks for the input. some great stuff! just remember, i am self taught access.
Oct 26 2004, 03:26 PM
I described the relationship in my explanation. When you add a record to the subform it would automatically fill in the LocationID. So all you might need is a pull down to the select which one of the "things".
Oct 26 2004, 04:42 PM
Iam an access self taught myself
Just a quick recomendation
do nothing with access or your project untill you learn about normalization
make up simple tables and use the normalization concept
it is one of those things where you have to see to understand ( atleast thatis how it was with me)
and i still dont know it all about normalization
Oct 26 2004, 07:03 PM
and i still dont know it all about normalization
the only person who knows all about Normalization is Dr Cobb (and maybe Jerry Dennison
Oct 26 2004, 11:57 PM
that was a figure of speech
what i meant to say was that i know nothing about normalization except for those little peices of information that gets me by in my silly little projects
Oct 27 2004, 02:51 AM
I was just teasing.
But don't think your projects are silly. If they make you more productive, then they are far from silly.
Oct 27 2004, 06:45 AM
what exactly is normalization?
Oct 27 2004, 07:26 AM
I suggested you search UA for threads about normalization. There is a lot of info on it. Its the KEY to designing relational databases. To put it in a nutshell its a process that reduces the amount of redundant data in your database. It also organizes your data more efficiently, making it easier to query them as is the case here.
Oct 27 2004, 08:01 AM
i see. so it is the next step in my learning process, huh?
thanks for the help scott. you will see me again heh.
Oct 27 2004, 08:13 AM
As SRT mentioned it should be the first step in the learning process. As your situation illustrates, proper design makes querying and reporting easier. Getting the design right is about 50% of a database app.
Oct 27 2004, 08:22 AM
i just tried to "normalize" one of my current databases... and i screwed it all up!
luckily i fixed it and i saved my info.
this is rough.
Oct 27 2004, 08:32 AM
First, before making any such drastic changes you should make a backup copy. Second, you need to understand normalization to some degree before you try doing it. If you are unsure, then post your structure and ask for advice first.
What did you do that you thought was normalizing?
Oct 27 2004, 08:37 AM
there is this normalization wizard, so i used it. i grabbed up a field that was used repeatedly and clicked it. a relationship formed, but when i went to a form i made, ,, it was all screwed up.
maybe if i had started that way in the beginning,, but now, it is just too much of a hassle.
oh well. guess i have a lot more learning to do. learning access is rough.
Oct 27 2004, 08:45 AM
The Normalization Wizard will split up your tables, but that's all it will do. It doesn't make any changes to forms, queries or reports that have already created. So any forms , etc. WILL need to be redone.
You will probably need to create main/subform setups to deal with related tables.
Yes, normalizing will be a hassle, but it will pay dividends in the long run.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here