How to select specific criteria from array

[Mr Edd]

Currently have 18 differently named fields of data but related in content and
to run queries/reports have to have one of each for each field.

I want to consolidate these fields into a single array where I can "ENTER
PARAMETER" to select only the records that contain that parameter but don't
know how to set it up.

Array would appear as 04, 06, 07, 08, 13 in one record and perhaps 04, 08,
13 in another and 04, 05 in another. Then be able to "ENTER PARAMETER of 13
and only get records 1 & 2 from these examples.

 

[Steve]

We need to see the 18 fields in your table.

Steve

 

[Mr Edd]

The 18 fields are identified as G01 (content 01); G02 (content 02); G03
(content 03) through G18 (content 18)

So I currently have a query and a report for selecting records with 01 in
G01, 02 in G02, etc for each of the 18 options.

Different records have different combinations of these options, One RECORD
might have 04 and 05. Another have 04, 06, 07, 08, 13.

But, I need to be able to generate a report for all the records that have an
04 irrespective of any of the other available numbers. on another occasion,
I wouild need a report for all and only those items have an "06", for example.

This is a rather large file and inputting the data into the individual
fields, where some fields get no data, is time consuming to be tabbing
through the unused fields.

Thus the application of an array where input could be consecutive seperated
by commas.

I just recently learned how do to the application of [ENTER xxxxx] criteria
for queries and have been able to delete nearly 300 individual inquiries and
reports that I can now handle with one general query/report and simply add a
few characters of data.

While I have your attention and appreciate your efforts I have another
matter that I don't know how to do.

For that same table, I have need for both date and day of the week on some
of the queries and reports. It is cumbersome to have to keep looking up the
day of the week to make manual entry since data is entered on random dates.

I also have a table of dates through the end of 2009 with the other field
being the day of the week.

I have been unable to successfully figure out how to join the two tables
so the day of the week will drop into place when the date is entered.

Is this possible.

Hope these descriptions make sense.

Very much appreciate any assistance you can give.

 

[Steve]

Your underlying problem is that your tables are not desaiogned correctly.
You need to have something like the following:

Your existing table needs to look like:

TblData
DataID
(remove the 18 fields)
<etc>

TblContent
ContentID
Content (Content01 to Content18 - 18 separate records!)

TblDataContent
DataContentID
DataID
ContentID

Set the following relationships:
1. DataID in TblData to DataID in TblDataContent
2. ContentID in TblContent to ContentID in TblDataContent

Records in TblContent look like:
1 Content01
2 Content02
3 Content03
4 Content04
etc

Records in TblDataContent look something like:
1 1 2
2 2 1
3 2 3
4 2 4
5 3 3
6 3 4
7 4 1
8 4 2
9 5 4
etc

Your data entry form needs to be a form/subform. The main form needs to be
based on TblData and the subform needs to be based on TblDataContent. Once
you create the form/subform, open the main form in design view, select the
subform control, open properties and be sure the Link Master and Link Child
properties under the Data tab are set to DataID. With the form/subform you
will be able to enter and display one or more contents for any data record.

Your query needs to include all three tables. From TblData you need the
fields you want from TblData to be in the report, from TblContent you want
the Content field and from TblDataContent you don't need any fields. In the
Criteria of the Content field you can then set the Content to one or more
contents.

Re: Weekday

You don't need the table of dates!!! Just add the following expression to
the first blank field in your query:
RecordWeekDay = WeekDay([NameOfYourDateField])
Then include the field RecordWeekDay in your report.

Steve
(e-mail address removed)

The 18 fields are identified as G01 (content 01); G02 (content 02); G03
(content 03) through G18 (content 18)

So I currently have a query and a report for selecting records with 01 in
G01, 02 in G02, etc for each of the 18 options.

Different records have different combinations of these options, One
RECORD
might have 04 and 05. Another have 04, 06, 07, 08, 13.

But, I need to be able to generate a report for all the records that have
an
04 irrespective of any of the other available numbers. on another
occasion,
I wouild need a report for all and only those items have an "06", for
example.

This is a rather large file and inputting the data into the individual
fields, where some fields get no data, is time consuming to be tabbing
through the unused fields.

Thus the application of an array where input could be consecutive
seperated
by commas.

I just recently learned how do to the application of [ENTER xxxxx]
criteria
for queries and have been able to delete nearly 300 individual inquiries
and
reports that I can now handle with one general query/report and simply add
a
few characters of data.

While I have your attention and appreciate your efforts I have another
matter that I don't know how to do.

For that same table, I have need for both date and day of the week on some
of the queries and reports. It is cumbersome to have to keep looking up
the
day of the week to make manual entry since data is entered on random
dates.

I also have a table of dates through the end of 2009 with the other field
being the day of the week.

I have been unable to successfully figure out how to join the two tables
so the day of the week will drop into place when the date is entered.

Is this possible.

Hope these descriptions make sense.

Very much appreciate any assistance you can give.

We need to see the 18 fields in your table.

Steve

 

[Jeff Boyce]

To paraphrase Steve's response, the way your data is organized will mean
both you and Access have to work extra hard to come up with kludges and
work-arounds.

If your data were well-normalized, Access' features/functions will work
quite well.

It's a bit of a "pay now or pay later" situation...

Regards

Jeff Boyce
Microsoft Office/Access MVP

The 18 fields are identified as G01 (content 01); G02 (content 02); G03
(content 03) through G18 (content 18)

So I currently have a query and a report for selecting records with 01 in
G01, 02 in G02, etc for each of the 18 options.

Different records have different combinations of these options, One
RECORD
might have 04 and 05. Another have 04, 06, 07, 08, 13.

But, I need to be able to generate a report for all the records that have
an
04 irrespective of any of the other available numbers. on another
occasion,
I wouild need a report for all and only those items have an "06", for
example.

This is a rather large file and inputting the data into the individual
fields, where some fields get no data, is time consuming to be tabbing
through the unused fields.

Thus the application of an array where input could be consecutive
seperated
by commas.

I just recently learned how do to the application of [ENTER xxxxx]
criteria
for queries and have been able to delete nearly 300 individual inquiries
and
reports that I can now handle with one general query/report and simply add
a
few characters of data.

While I have your attention and appreciate your efforts I have another
matter that I don't know how to do.

For that same table, I have need for both date and day of the week on some
of the queries and reports. It is cumbersome to have to keep looking up
the
day of the week to make manual entry since data is entered on random
dates.

I also have a table of dates through the end of 2009 with the other field
being the day of the week.

I have been unable to successfully figure out how to join the two tables
so the day of the week will drop into place when the date is entered.

Is this possible.

Hope these descriptions make sense.

Very much appreciate any assistance you can give.

We need to see the 18 fields in your table.

Steve