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

Hi,

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.

HTH
Chris

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.

thanks again.

Hi,

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.

Chris

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.

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?

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,

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.

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".

Iam an access self taught myself

Just a quick recomendation
do nothing with access or your project untill you learn about normalization
then
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

the only person who knows all about Normalization is Dr Cobb (and maybe Jerry Dennison (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif) )

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

I was just teasing.

But don't think your projects are silly. If they make you more productive, then they are far from silly.

what exactly is normalization?

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.

i see. so it is the next step in my learning process, huh?

thanks for the help scott. you will see me again heh.

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.

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.

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?

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.