Need suggestions on how to do this.

[Guest]

I have a table that has grown into too mnay fields and I don't have the time
to re-do things. Part of the problem I now have is that I need to report on
some data that is in the following fields and format:

Unit1 : True/False
Unit1Region : Single Digit (text)
Unit1Station : Two Digits (text)
Unit1Shift : Single Alpha (text)
Unit1ShiftNumber : Single Digit (text)
Unit1CAMT : Text (15 Characters max)
Unit1Form2 : Text (15 Characters max)

And the same fields for Unit2, Unit3 and Unit4.

The problem I now have is that I have been asked to provide a report on the
Units where the fields Unit#CAMT and / or Unit#Form2 are blank.

What I have tried to do is write a criteria string for a Open document
command that says:

if Unit# is True and Unit#CAMT is Null
or
If Unit# is True and Unit#Form2 is Null

then display the fields in the report. However, I have not had any luck
getting even Unit1 to report properly. And on top of this I need to look at
all four units in each record row and report all of the ones where Unit# is
True.

I am thinking that I need to copy all the record rows that match the
criteria into a temporary table and bind my report to that table. But I don't
know how to do this, or even if this is the best way to go. Perhaps I need to
redesign the database and break the main table into several smaller one.


Any ideas would be appreciated.

Cheers

 

[strive4peace]

Hi Paul,

You will save yourself much time down the road if you
normalize your data now, even though it will take time from
your immediate tasks.

To answer your question, the report OPEN event is too late
to change the RecordSet. The preferable way would be to
open the feport using the optional WHERECONDITION clause

DoCmd.OpenReport
reportname
[, view]
[, filtername]
[, wherecondition]


Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Rick Brandt]

Hi Paul,

You will save yourself much time down the road if you normalize your data now,
even though it will take time from your immediate tasks.

To answer your question, the report OPEN event is too late to change the
RecordSet. The preferable way would be to open the feport using the optional
WHERECONDITION clause

If you meant the RecordSource then the Open event is not too late. That is done
all the time.

 

[Guest]

Thanks for the comments. So now I have a couple of questions.

First, I am thinking of using a SQL INSERT command after DoCmd.SaveRecord to
write the values of the Unit information into the new tblVehicles. How do I
get the autonumber [RecordID] from the tblEpistry of the record I just saved
to insert it with the Unit information so that Unit info is linked to the
Epistry record?

Second, is this the best way to do this or should I be doing something else?
I am going to write one new record to the tblVehicles for each Unit that has
a TRUE value for the UnitNumber ie: Unit1 through Unit4. This way when I pull
a report, the report will look to see if Unit1 is True, then pull that
vehicle info from the row in the tblVehicles that a) matches the RecordNumber
and b) matches the UnitNumber. So there could be only one vehicle or up to
four.

I hope I have made this clear enough. Thanks for taking the time to look at
this.

 

[strive4peace]

Hi Paul,

1. After your SQL INSERT,

currentdb.tabledefs.refresh
DoEvents

then, to get the recordID that was just created:

dim mRecordID as long
mRecordID = dMax("RecordID","tblEpistry")

instead of using DoCmd.SaveRecord, you can use this form
which is better since you can specify which form is saving
the record:

if me.dirty then me.dirty = false

"me" can be any valid form reference

2. "is this the best way to do this?"

I am not sure what you need so it is hard to say.
Personally, I only put records in tables when there is data
for that record. I also HIGHLY advice you restructuring to
normalize. It really doesn't take long to convert it and
your life will be so much easier

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Guest]

Hi Paul,

Crystal advise to normalize your database now is the best time-saving advise
you could have received from anyone. You really should do so.

However, in the interim, you could create a Union query to use as the
recordsource for your report. Add the appropriate criteria in the final
SELECT statement of the union query. If you Google the
microsoft.public.access.* newsgroups for the terms:

Union Query Normalize

http://groups.google.com/groups?as_...981&as_maxd=7&as_maxm=5&as_maxy=2006&safe=off

You should be able to find examples of how to create a union query to handle
the situation of non-normalized data structures.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________