SOLVED: Add Field to Report

let me please start by apologizing... i took an Access class about 3 years ago and was so confused i tucked it away in my brain... I then learned Crystal Reports and absolutely love it... now i am having to use Access and can not remember a thing! i have signed up for another class, but in the meantime i am flying solo and about to hit the eject button...

my problem:
i created a database with 6 tables in it. linked them all up correctly, and then started to try and figure out how to write a report... i decided it was safer to use the wizard the first time and hopefully somethings would come back to me... i was able to get the primary report finished and got the data to the people that were asking for it, but then i went back to try and resort it another way and that is where the problem comes in...

when i started the report in the wizard, it told me to pick the fields i wanted to display in the report - so i did... i did not think i would need this particular field at the time. now when i click on the FIELD LIST box the only fields that are in there are the ones i originally chose. i have gone into the SQL design grid and added it there after reading that in another forum, but that really screwed things up.. all i want to do is add a simple string field to the report... i am already using the table within the report; just not that field...

ironically, after fighting with this for days, i discovered a way to do the report in Crystal, however, i can not remember all the steps i took to get the results i got in the Access report. i thought there would be a "view SQL query" option like there is in Crystal, or maybe a "report selection crietria" section... i can not find either of those so i can get part way to the final result in Crystal but not completely there - and in Access i cant get it to sort the way i want...

any help whatsoever would be so greatly appreciated...

i feel totally :bug: ...

If you can post a stripped down version of your db here I'll take a look for you.

I sounds to me like you have added a field to a table, and now you want to report on it in your report? The reason it not appearing in your field selection list is this.

When you associate a table or query to a report(or form), access build a SQL string associated to the table as it is built at that time. So even though you added a field to the table, the SQL string doesn't show that field as existing. To resolve the problem do this.
1. Open your report
2. Right click on the gray box in the top left corner of the design grid.(It should turn black when you click.
3. Select properties.
4. Click on the data tab
5. You will see the SQL string in the record source field(Don't worry you don't need to edit the string)
6. Click on the record source field
7. Click on the ... button that appears.
8. You should now be in a query design grid.
9. Drag the field from the table to the query grid.
10. Close the query.
11. Check your field selector. The field should now be visible.

HTH.

i did not add the field to the table - it was always part of the table, i just did not pull it over when the wizard asked me what fields i wanted in the report... that is why i am so confused...

i started to strip down my test database the other day... i am just trying to figure out what fields i need to leave so you can make sure the linkage is correct if you try to test anything... with it being a patient database i am trying to remove as much information as possible because of HIPAA and such...

in the meantime i have been going in and sorting manually whenever i get the chance...

thanks for helping - we will figure it out someday lol

cmpgeek,
Try the same solution. The wizard is only bringing in the fields you selected for the SQL statement. That's why the other fields don't show up in the field selector.

i have actually tried this suggestion before as well... ANYTIME i open the SQL deisgn grid - whether i make changes or not - ANY time i open it and then close it i go from having 255 pages of data to 10 pages of data... i dont know what happens, or why all of a sudden it doesnt see the rest, but the preview goes from 255 pages down to 10...


me -> :confused: + Access = :drunkard:
(we dont have the one hitting his head against the brick wall LOL)

Quote:

---

Originally Posted by cmpgeek

i thought there would be a "view SQL query" option like there is in Crystal, or maybe a "report selection crietria" section... i can not find either of those so i can get part way to the final result in Crystal but not completely there - and in Access i cant get it to sort the way i want...

---

I don't know if you figured this out already, but any time you're in the Query Design view (where you can see the design grid at the bottom and the field list at the top), you can always go to the top of the window, push the "View" button and choose "SQL View". I hope that's at least some help.

Open the query in design view, double click the field from the table you want to appear in the report to add it to the query. Open the report in design view and drag the control from the Field List onto the report.
If that doesn't work, post your db here deleting ALL data within the tables and I'll take a look.

HTH

I have had a couple other projects come up and have finally gotten into the Access class... i have not had a chance to look back at this since then, but i will try it again soon... thanks to everyone for your help...

i know this is not technically solved yet, but i think i will mark it as such so that yall wont worry about it right now...