Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dosey of a problem. I have a form that I enter information onto.
This information is put into a report that I have designed. I only have 13
spots for the fields to show. If there are more than 13 spots I need it to
continue onto another part of the report. How could I do this. I thought of
creating 2 queries. But I don't know how to limit it to the first 13. I
have them in date order.

Thanks for your time

Chey
 
I have a dosey of a problem. I have a form that I enter information onto.
This information is put into a report that I have designed. I only have 13
spots for the fields to show. If there are more than 13 spots I need it to
continue onto another part of the report. How could I do this. I thought of
creating 2 queries. But I don't know how to limit it to the first 13. I
have them in date order.

Thanks for your time

Chey
Click to expand...

Chey, data is NOT stored in Forms. Data is NOT stored in Reports. You
cannot enter data into a Report from a Form.

Data is stored in TABLES - and only in Tables.

Design your table structure so that it contains the information that
you need to report (this will quite possibly require two or more
tables; I don't know your data needs so I can't say). Make sure these
tables are properly normalized; if you don't know the meaning of the
term, read about it (see below).

THEN create a Form to enter data into those tables. Only then should
you create a Report.

It sounds like you started with the thirteen-item report and worked
backwards. That's pretty much a guarantee to get a BAD design!

John W. Vinson[MVP]
 
Ok maybe I should rephrase what I ment. I do have them in a table. In a
query Can I limit from example record 1-13 and then 14-50? If I can't then I
will just add another table and add it to that query for my form. I am
storing the information in a table. I am viewing it in a form.
 
Ok maybe I should rephrase what I ment. I do have them in a table. In a
query Can I limit from example record 1-13 and then 14-50? If I can't then I
will just add another table and add it to that query for my form. I am
storing the information in a table. I am viewing it in a form.
Click to expand...

Well, there are no "record numbers" in Access tables, so as phrased,
no you can't. If you have some field (an autonumber maybe?) that is
unique and in the order you want, you can use the Top Values property
of a query to select thirteen records if you wish.

What's magic about 13? How do these 13 records differ from the other
37? Are you encoding meaningful information in the record's position
in the table - if so, DON'T!, you can't rely on it!

John W. Vinson[MVP]
 
What I have done is in reports I have created a design of my own. I created
it under reports based off of a company form I use. It allows me 13 rows to
display idomized cost. I then have what we call a continuation page that
allows for the rest of the idomized amounts. Off of forms in Access I type
the information into a table. When I run a command button it prints the
information onto the report I have designed. I have a where function that
pulls the right Travel Authorization Number. When I run the query I have
them in date order. If I am not able to extract the first 13 can I put a
limit of how many records I use for each TA Number? So I can only have 13 of
the same TA Number? Then I can create another table that will allow for the
rest. This will work for me. However I don't what to physically put things
in date order, I want the query to do it for me. Thanks for taking time in
trying to help me.

Chey
 
What I have done is in reports I have created a design of my own. I created
it under reports based off of a company form I use. It allows me 13 rows to
display idomized cost. I then have what we call a continuation page that
allows for the rest of the idomized amounts.
Click to expand...

It SOUNDS like you may want to launch two reports: one for the (usual)
13-line report, and a second report, with a different layout, for the
continuation page. I'm not much of an expert in Reports, so you might
want to post a description of the problem on the
microsoft.public.access.reports newsgroup; it may well be possible to
have a single report with two pages.
Off of forms in Access I type
the information into a table. When I run a command button it prints the
information onto the report I have designed. I have a where function that
pulls the right Travel Authorization Number. When I run the query I have
them in date order. If I am not able to extract the first 13 can I put a
limit of how many records I use for each TA Number?
Click to expand...

You can set the Query's Properties to set its "Top Values" propserty
to 13. This will limit the report to 13 records.
So I can only have 13 of
the same TA Number? Then I can create another table that will allow for the
rest.
Click to expand...

You certainly do NOT need to create a new table.... sheesh!!! Reports
ARE NOT TABLES. You just need the data in your (properly normalized)
tables. Reports are based on Queries.
This will work for me. However I don't what to physically put things
in date order, I want the query to do it for me.
Click to expand...

Well... yes. Your best bet for putting things in date order on a
Report is to use the Report's "Sorting and Grouping" property to sort
by date, and/or whatever other fields you want to use. Computers are
very good about sorting data; there is NO reason you should ever need
to manually sort records yourself.

John W. Vinson[MVP]
 
okay I am finally getting somewhere. I figured out what you ment by top
values. I was able to do that. Now is there away to get the rest. I read a
little about it. How can I get records 14-end? The next part of my self
made report can hold upto 50 records. I will never use that many. Anyways
thanks for being patient with me. Can you please explain how to get the
remain records. I am creating a new query for this one.

Thanks again.
Chey
 
okay I am finally getting somewhere. I figured out what you ment by top
values. I was able to do that. Now is there away to get the rest. I read a
little about it. How can I get records 14-end? The next part of my self
made report can hold upto 50 records. I will never use that many. Anyways
thanks for being patient with me. Can you please explain how to get the
remain records. I am creating a new query for this one.
Click to expand...

It's a slightly more complicated query. Let's say you have your
13-record top values query (named qryReport let's say) and it contains
a field named ID which is the Primary Key of your table. (If you table
doesn't have a primary key, you're in need of some redesign - every
table should).

Create a new query based on the table - just copy your current query
I'd guess, removing the Top Values property. Put on the Criteria line
under the ID field

NOT IN(Select [ID] From qryReport)

and put whatever other criteria are needed to get it to display only
the desired records.

John W. Vinson[MVP]
 
That worked
Thanks a bunch
Chey

John Vinson said:
okay I am finally getting somewhere. I figured out what you ment by top
values. I was able to do that. Now is there away to get the rest. I read a
little about it. How can I get records 14-end? The next part of my self
made report can hold upto 50 records. I will never use that many. Anyways
thanks for being patient with me. Can you please explain how to get the
remain records. I am creating a new query for this one.
Click to expand...

It's a slightly more complicated query. Let's say you have your
13-record top values query (named qryReport let's say) and it contains
a field named ID which is the Primary Key of your table. (If you table
doesn't have a primary key, you're in need of some redesign - every
table should).

Create a new query based on the table - just copy your current query
I'd guess, removing the Top Values property. Put on the Criteria line
under the ID field

NOT IN(Select [ID] From qryReport)

and put whatever other criteria are needed to get it to display only
the desired records.

John W. Vinson[MVP]
Click to expand...
 
I have another question that stems off of the one before. How can I sum up
the 13 records on the query? I have done this before, but now I can not
figure it out.
Then I will need to sum up the remaing 14+ records in the other query.
Thanks

John Vinson said:
okay I am finally getting somewhere. I figured out what you ment by top
values. I was able to do that. Now is there away to get the rest. I read a
little about it. How can I get records 14-end? The next part of my self
made report can hold upto 50 records. I will never use that many. Anyways
thanks for being patient with me. Can you please explain how to get the
remain records. I am creating a new query for this one.
Click to expand...

It's a slightly more complicated query. Let's say you have your
13-record top values query (named qryReport let's say) and it contains
a field named ID which is the Primary Key of your table. (If you table
doesn't have a primary key, you're in need of some redesign - every
table should).

Create a new query based on the table - just copy your current query
I'd guess, removing the Top Values property. Put on the Criteria line
under the ID field

NOT IN(Select [ID] From qryReport)

and put whatever other criteria are needed to get it to display only
the desired records.

John W. Vinson[MVP]
Click to expand...
 
I have another question that stems off of the one before. How can I sum up
the 13 records on the query? I have done this before, but now I can not
figure it out.
Then I will need to sum up the remaing 14+ records in the other query.
Click to expand...

Put a textbox in the Report Footer, and set its Control Source to

=Sum([fieldname])

using the name of the field you want to sum. You can put more than one
textbox if you want to sum more than one field.

Or, use the Report's Sorting and Grouping dialog (rightclick the
little square in the upper left in design view) to group by one or
more fields, and sum the values for that grouping. Again you would
make the Group Footer visible and put a textbox in it with the
=Sum(...) control source.

John W. Vinson[MVP]
 
Im Back. I did what you told me to do, and now it says query to complex. Is
this something to worry about?
I have it assending off of the service date, then top values 13.
Thanks
Chey

John Vinson said:
I have another question that stems off of the one before. How can I sum up
the 13 records on the query? I have done this before, but now I can not
figure it out.
Then I will need to sum up the remaing 14+ records in the other query.
Click to expand...

Put a textbox in the Report Footer, and set its Control Source to

=Sum([fieldname])

using the name of the field you want to sum. You can put more than one
textbox if you want to sum more than one field.

Or, use the Report's Sorting and Grouping dialog (rightclick the
little square in the upper left in design view) to group by one or
more fields, and sum the values for that grouping. Again you would
make the Group Footer visible and put a textbox in it with the
=Sum(...) control source.

John W. Vinson[MVP]
Click to expand...
 
Im Back. I did what you told me to do, and now it says query to complex. Is
this something to worry about?
Click to expand...

Only if you want the query to work. Yes, it *is* something to worry
about; it means the query doesn't run!
I have it assending off of the service date, then top values 13.
Click to expand...

Please post the SQL.

John W. Vinson[MVP]
 
Okay it does not say that anymore. Now, on my continuation page, if there is
nothing on it, how to I get my sum in the footer to just be 0. this is what
I have right now. =Sum(Nz([Hotel_Amount],0))

I have this for 4 items, Hotel, Credit Card, Cash, and Per Diem. They are
all formated the same. Once I have this then I think everything should be
okay.
Thanks so much for all your help.
 
now the problem I am running into with the top values 13 is what to sort it
by. Or do I even sort by anything. I am meaning in the query telling
something assending.
I get a different outcome each time with each person.
 
now the problem I am running into with the top values 13 is what to sort it
by. Or do I even sort by anything. I am meaning in the query telling
something assending.
I get a different outcome each time with each person.
Click to expand...

I don't know, Chey.

I don't know anything about your data.
I don't know what you want it sorted by.

Yes, it should be sorted but please don't ask me to tell you what YOU
want to do with YOUR data.

John W. Vinson[MVP]
 
Back
Top