Re: the best way to know events

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Yes, it's hard to change structure after building everything.

Clumsy: not as easy to create or maintain: you can't do UNIONs in design view. Also, results are read-only.

Slow: depends how many records you have. Should be fine with a few thousand, possibly even tens of thousands. But it does get slower as the number of records increases.

Filtering: you could save the UNION, and then create another query that uses the UNION query as its source 'table.' You can then filtere on EventDate, just as you would any other query.

That's where the performance falls off. You are UNIONing all the records into one long list, and then filtering on a calculated field. That's going to be much slower than a correctly normalized table where you can create in index on the date field. But again: it depends on the number of records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Frank Situmorang" <hfsitumo2001@xxxxxxxxx> wrote in message
news:5C7B4024-107E-42EF-BBCD-96C508CFFFF8@xxxxxxxxxxxxxxxx
Thanks Allen, I will try to make Union Query, because to change the structure
would be very difficult I think since all my forms and reports I already
finished and even to make the reports and forms in foreign language is also
almost finished.

But wen you say clumsy it will work very slow?. I have desingned the
membership database to be able to be used in the consolidation of all
membership per region. So will it be very slow if huge records exist?.

Any way, at least this feature can be used in the church level.

When I used this Union Query how can we filter it for Jan 2009, or Feb 2009
only. Because union query can not be viewed in the designed view. Normally in
designed view we just put in the grid of criteria, the filter.

Thanks for your help again,

--
H. Frank Situmorang


"Allen Browne" wrote:

Frank, a relational data structure would store these dates in a related
table rather than as many fields in the one table.

The related table would have fields like this:
MemberID relates to your table of members
EventTypeID relates to a table of event types.
EventDate date/time
You would also have a table of event types, with a *record* for each type
(e.g. "Birth", "Marriage", "Baptism", ...)
With that structure, is it very easy to filter the EventDate (or crosstab
it), and get a count of members for each type.

If you cannot change the structure, an inefficient and clumsy workaround
might be to create a UNION query:

SELECT MemberID,
[Date of Birth] AS EventDate,
"Birth" AS EventType
FROM tblMember
WHERE [Date of Birth] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Marriage] AS EventDate,
"Marriage" AS EventType
FROM tblMember
WHERE [Date of Marriage] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Baptized] AS EventDate,
"Baptism" AS EventType
FROM tblMember
WHERE [Date of Baptized] Is Not Null
UNION ALL
SELECT MemberID,
[Date of Death] AS EventDate
"Death" AS EventType
FROM tblMember
WHERE [Date of Death] Is Not Null;

"Frank Situmorang" <hfsitumo2001@xxxxxxxxx> wrote in message
news:31E63DE9-035B-419F-AD64-71E278BCEF41@xxxxxxxxxxxxxxxx
> Hello,
>
> In my church membership database, I have field name: Name; Date of > Birth,
> date of Marriage; date of Baptized, date of death.
>
> My aquestion is what is the best way to know the events, say, how many
> babies were born or member baptized, etc.. during February 2009. is > there
> any
> best way to filter?. Is there any sample of query to make like a > crosstab?
> a the report will show like this:
> jan feb mar apr......................dec 09
>
> Birth
> Baptized
> Marriage
> Death
>
> Thanks for any help
> -- > H. Frank Situmorang



.

Relevant Pages

  • Re: the best way to know events
    ... usula select query and then on the date field in the query I just used the ... You can then filtere on EventDate, ... When I used this Union Query how can we filter it for Jan 2009, ...
    (microsoft.public.access.formscoding)
  • Re: Transposing data
    ... Client ID Eventdate ... You can use a union query to select each year. ... FROM tblName ...
    (comp.databases.ms-access)
  • Re: creteria macros
    ... Having written the Union Query, if you have control over the Tables, I suggest basing a Make-Table Query on the Union Query and creating a Table containing all the records from the separate Tables. ... Class1_ID STATUSFIELD DATEFIELD ... So let's enter the new creteria once for Date and Status field and have it forwarded to each of the queries for CLASS1 through to CLASS8. ...
    (microsoft.public.access.queries)
  • Re: Query is too complex
    ... And if your table have one field for each compound, ... of queries to populate it as needed. ... to use the Union queries that are working now. ... I also developed the union query to union together those 2 queries. ...
    (microsoft.public.access.queries)
  • Re: Union Query
    ... I definitely understand you comment about normalization. ... What happens with the "union" table is that it get's linked to ... here's the essentials of how to build a Union query. ... Each SELECT subquery may have other clauses (eg. ...
    (microsoft.public.access.queries)