Repost 2nd Time

  • Thread starter Thread starter eatc7402 via AccessMonster.com
  • Start date Start date
E

eatc7402 via AccessMonster.com

I am using Access 2002. I am writing a query can creates a 'flat' result from
three other joined tables. One field is a memo field that contains a large
amount of data, especially when it is recreated over and over as a result
of the 'flattening' of the data.

Is there a way to add a criteria that will ONLY fill the memo field when
IT CHANGES DATA. In other words I am look for a way to control a fields
duplication to only fill the memo field under my control at query design time.


I have been searching for a method to do this but seem to be 'lost in the
trees' over what should be a rather simple thing to do (I hope).

eatc7402
 
Is there a way to add a criteria that will ONLY fill the memo field when
IT CHANGES DATA. In other words I am look for a way to control a fields
duplication to only fill the memo field under my control at query design time.
Click to expand...

Not that I know of. You could use a SELECT DISTINCT but it will
truncate the memo field to 255 bytes; if the two memo fields differ in
the 256th or later position they'd be seen as duplicates.

The problem is that a Memo could be as big as a billion bytes, and
Access isn't programmed to compare two billion-byte fields to see if
they differ in the 984,224,312th byte.


John W. Vinson [MVP]
 
Does calling a VBA Function from within the query allow one to by-pass the
normal truncation, and you could compare all the bytes within the VBA routine?
 
Does calling a VBA Function from within the query allow one to by-pass the
normal truncation, and you could compare all the bytes within the VBA routine?
Click to expand...

I haven't done so but I'd expect so.

In the other thread you're talking about "populating" a Memo field. Of
course, a Select query doesn't populate any fields. Could you step
back a pace and describe what you're starting with and what you're
trying to accomplish? Are you actually inserting new records into a
table, and trying to only insert a Memo field into one (new!) record
in this table? If so, why are you trying to store the data redundantly
in a second table *at all*?

John W. Vinson [MVP]
 
Yes, I'd like to know too. You are flatening 3 tables into 1.
So it's my understanding that you have a memo field in each of the three
tables, and if each memo contains the same data as the other two, then you
would only want the results of one of them to go into the flatenned file,
right?

EXAMPLE ONE's MEMO FIELDS

Tbl1 = happy
Tbl2 = happy
Tbl3 = happy

FlatTbl's Single MEMO FIELD = happy

EXAMPLE TWO's MEMO FIELDS

Tbl1 = happy
Tbl2 = not happy
Tbl3 = not really happy

FlatTbl's Single MEMO FIELD = happy, not happy, not really happy
 
Access101 said:
Yes, I'd like to know too. You are flatening 3 tables into 1.
So it's my understanding that you have a memo field in each of the three
tables, and if each memo contains the same data as the other two, then you
would only want the results of one of them to go into the flatenned file,
right?

EXAMPLE ONE's MEMO FIELDS

Tbl1 = happy
Tbl2 = happy
Tbl3 = happy

FlatTbl's Single MEMO FIELD = happy

EXAMPLE TWO's MEMO FIELDS

Tbl1 = happy
Tbl2 = not happy
Tbl3 = not really happy

FlatTbl's Single MEMO FIELD = happy, not happy, not really happy
[quoted text clipped - 10 lines]
John W. Vinson [MVP]
Click to expand...
Click to expand...


No, the join is of three tables.... one master record containing the memo
field,
and two subordinate tabes that contain related subinformation. Thus, I get
the
'master record' memo field duplicated over and over (undesireably) into the
flat
table result.

eatc7402
 
No, the join is of three tables.... one master record containing the memo
field,
and two subordinate tabes that contain related subinformation. Thus, I get
the
'master record' memo field duplicated over and over (undesireably) into the
flat
table result.
Click to expand...

Again, please:

IS THIS A MAKE-TABLE or APPEND query?

Are you trying to *store* the memo field into a new table?

If so... *why*?


If the query is just a Select query, you're not "storing" the result
anywhere.

Could you post the SQL of the query, and perhaps give an example of
the desired result?


John W. Vinson [MVP]
 
If you joined only to one of the subordinate tables, and it had 2 related
records, would the master repeat twice in the query results?

If the related table had 5 records, would the master record repeat 5 times
in the query? SELECT DISTINCTROW, SELECT DISTINCT
 
John said:
No, the join is of three tables.... one master record containing the memo
field,
Click to expand...
[quoted text clipped - 3 lines]
flat
table result.
Click to expand...

Again, please:

IS THIS A MAKE-TABLE or APPEND query?

Are you trying to *store* the memo field into a new table?

If so... *why*?

If the query is just a Select query, you're not "storing" the result
anywhere.

Could you post the SQL of the query, and perhaps give an example of
the desired result?

John W. Vinson [MVP]
Click to expand...


Yes it is a make table query, with trhe flatened resulting table being
exported to MySQL on
the web.

Heres the SQL of the query...

SELECT extra_alarm_fires.city_name AS Expr1, extra_alarm_fires.fire_year,
extra_alarm_fires.extra_alarms_sort_order, extra_alarm_fires.fire_date,
extra_alarm_fires.month_no_fires_flag, extra_alarm_fires.max_alarms,
extra_alarm_fires.box_or_location_id, extra_alarm_fires.run_number,
extra_alarm_responces.first_rec_flag, extra_alarm_responces.last_record_flag,
extra_alarm_fires.address_location, extra_alarm_fires.vac_or_occupied,
extra_alarm_fires.occupancy_type, extra_alarm_fires.fire_description,
extra_alarm_responces.sort_order_responces, extra_alarm_responces.alarm_time,
extra_alarm_responces.alarm_type, companies_table.engines, companies_table.
trucks, companies_table.squads, companies_table.other, companies_table.chiefs,
companies_table.notes
FROM companies_table RIGHT JOIN (extra_alarm_fires RIGHT JOIN
extra_alarm_responces ON extra_alarm_fires.extra_alarm_pri_key =
extra_alarm_responces.fires_pri_key_copy) ON companies_table.link_to_alarms =
extra_alarm_responces.responces_key
ORDER BY extra_alarm_fires.city_name, extra_alarm_fires.fire_year DESC ,
extra_alarm_fires.extra_alarms_sort_order, extra_alarm_responces.
sort_order_responces;


eatc7402
 
eatc7402 said:
[quoted text clipped - 17 lines]
John W. Vinson [MVP]
Click to expand...

Yes it is a make table query, with trhe flatened resulting table being
exported to MySQL on
the web.

Heres the SQL of the query...

SELECT extra_alarm_fires.city_name AS Expr1, extra_alarm_fires.fire_year,
extra_alarm_fires.extra_alarms_sort_order, extra_alarm_fires.fire_date,
extra_alarm_fires.month_no_fires_flag, extra_alarm_fires.max_alarms,
extra_alarm_fires.box_or_location_id, extra_alarm_fires.run_number,
extra_alarm_responces.first_rec_flag, extra_alarm_responces.last_record_flag,
extra_alarm_fires.address_location, extra_alarm_fires.vac_or_occupied,
extra_alarm_fires.occupancy_type, extra_alarm_fires.fire_description,
extra_alarm_responces.sort_order_responces, extra_alarm_responces.alarm_time,
extra_alarm_responces.alarm_type, companies_table.engines, companies_table.
trucks, companies_table.squads, companies_table.other, companies_table.chiefs,
companies_table.notes
FROM companies_table RIGHT JOIN (extra_alarm_fires RIGHT JOIN
extra_alarm_responces ON extra_alarm_fires.extra_alarm_pri_key =
extra_alarm_responces.fires_pri_key_copy) ON companies_table.link_to_alarms =
extra_alarm_responces.responces_key
ORDER BY extra_alarm_fires.city_name, extra_alarm_fires.fire_year DESC ,
extra_alarm_fires.extra_alarms_sort_order, extra_alarm_responces.
sort_order_responces;

eatc7402
Click to expand...
It's the fire_decription field that is the 'problem child' field,.

eatc7402
 
It's the fire_decription field that is the 'problem child' field,.
Click to expand...

Hrm. MySQL is just as relational as Access - I'm wondering why you
need to flatten. That said... you're getting multiple records because
of the multiple join tables, and I take it you want to see the content
of the Memo field only in the first record where it appears, and NULL
in the subsequent records? What about all the OTHER repeating fields?
They should just repeat?

That's a challenging query; I don't see (at 10:30pm after a difficult
class in church history) an immediate answer, now that I finally think
I *do* understand the question. I'll mull on it overnight and see if I
can come up with a good idea tomorrow!

John W. Vinson [MVP]
 
It appears from another newsgroup that you have a solution. If not
please post back as a new thread.

John W. Vinson [MVP]
 
Back
Top