A form that changes a query that changes a report

[mehgan]

Hello everyone! I am compiling a database that will constantly be in a state of change. I have completed my form which automatically updates my table. I have created my queries, which are automatically updating when the table updates?!?! And I have created my reports that are based on my queries...but at this point some of the data is incomplete. So, when I made the report the particular year(s) (that in the future) may become something that need to be included in the report are currently not included. I was talking to someone and they said I could write a VBA code to correct this problem??? Is this true?
Basically there are only three things in my report...
Item/Park (row)
Repl Year (column heading) (which stands for replacement year)
Cost (value) and it is a SUM

I would like for my reports to update any NEW ITEMS that are not included in the database at this point and any Repl Years that are not included. My Years range from 1982-2036, but if there is not an ITEM that needs to be replaced so far in a certain year than that year doesn't show in the query, thus isn't an option for the report (in the report wizard). I there a way that I can resolve this or do I just have to re-do the reports when it comes time?? [BTW I am using Access 97]
Thank you in advance for any help that you can give me.
-mehgan

 

[WildHare]

Hi Meghan;

I am compiling a database that will constantly be in a state of change.
Welcome to the real world...

when I made the report the particular year(s) (that in the future) may become something that need to be included in the report are currently not included.
Can I have some of what you're smoking?...

I was talking to someone and they said I could write a VBA code to correct this problem??? Is this true?
Probably not.

any NEW ITEMS that are not included in the database at this point and any Repl Years that are not included. My Years range from 1982-2036, but if there is not an ITEM that needs to be replaced so far in a certain year than that year doesn't show in the query,

Ok, I THINK what you're describing is the typical 'How can I display data that's not there yet' dilemma, right?

Are you using a query with a join to extract your data? If so, it's probably doing an INNER JOIN, e.g. only data that's in both sets of data - so if there is a "2005" in one table but no "2005" in the other, No "2005" in your report. But you want a LINE for 2005, even if it's blank? To indicate that yes, you are indeed thinking about what might happen in 2005?...

Easy. Open the query in design mode. Right Click the Join line. Change the JOIN to a (and here's where it gets tricky, because I don't know how you've done this) a #2 join (LEFT) to pull ALL the YEARS and any REPAIRS that match. It MIGHT be that you need a #3 type instead, but I can't tell without knowing how you did your query.

But you should recognize which type you need when you right-click and get the dialog to change join types. If not, write back and we'll attack it again...

If you haven't yet, you might take a look here:

http://www.jmhare.com/joins.htm

Hope this helps.


Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[demoman]

Mehgan

Hopefully, I am understanding your question. It concerns your inability to complete your design because it is predicated upon events which have yet to take place? Is this correct? If so, you might want to take another look at your design.

There are many applications which are similar to what you describe. A good example is a reservation application. A reservation is made for a specific point in time (let's say day and hour). So a simplified Reservation table may look like:

Customer Date Time
--------------------------------
Mike Jones 04/16/02 1:00 PM
Jill Smith 04/20/02 7:00 AM

Now, if you base a DAILY report off this data, you only would see the two days where there are reservations. In most cases, people want to see every day (and hour in this case). Therefore, your report is driven by TIME, not by the Reservation table. And time is something which can be designed into you app now, and (if done right) will not require future modification.

Does this make sense, or did I miss your question?

 

[mehgan]

Okay, I thought that I had found a solution to my problem, but now I am getting an error message. I am running my reports off a crosstab query...and the row IS automatically updating when I make changes to the table. BUT the Repl Year (which is a column) will not. SO, I thought if I put fake #s in the table and then pulled them into my report(as a control source)that would solve the problem. BUT once I erase the FAKE figures from the table they erase from the query and so I get the following message when trying to open the report:
"Run-time error '3070':
The Microsoft Jet database engine does not recognize '[1983]' as a valid field name expression."

Is there a way that I can write a WHERE clause or something in the code builder to correct this? At this point, that date (and about 5 others) are not valid replacement years...but they COULD contain a value once all of the data is entered. Is there a way to write something into the query to overlook this until there is a value entered if ever??
Thanks for any advice...Mehgan