How to query a Dataset to Return Records that contain "a thing"

[Nick]

OK, let me explain the subject a little better. Please note, I have not yet
built the query, I am still working on the "how to" part of it.

I have a table that stores Purchase Order information. This is a LARGE table
and contains all the relevant information of one Purchase Order. It is
essentially the "End Product" of the entire Database (its reason for being).
The table is the main driver to support financial management, data tracking
and to - pertinant for this question - review all suppliers for past
performance. As such, the table includes the Purchase Order Decision
information and quotation information.

I also have a table that is a list of all the Companies/Suppliers we deal
with. Nice and simple table, nothing out of the ordinary. Oh, and this table
has the Primary Key as the Company Name. This has been set after MUCH
consideration of the naming convenstions for companies for us. What it means,
for this problem, is that the Company Name in the Purchase Order Table
(selected by drop down) is a WYSIWYG data field.

In order to "cross reference" and pull up all the records in the Purchase
Order table that relate to a specific company I use a Form with a Sub Form
(the Form is based on the Company Table, while the Sub-Form is the Purchase
Order Table). This allows personnel to review previous company performance
FOR THE CURRENT FINANCIAL YEAR.

This is where the problem starts. At the end of each FY, the information in
the Purchase Order Table is ammended to another table (called Past Purchase
Order Information). All the Company Names are transferred to the Past
Purchase Order Table and the Past Purchase Order Table does not have any
relationships with anything - it is just a data table for datamining. The
Purchase Order Table is then reset for the new Financial Year.

The Purchase Order, and Past Purchase Order tables uses the Company Name as
a drop down field in six seperate fields (each named differently in the
Purchase Order Table). These fields are:

Quotation 1 Company Name
Quotation 2 Company Name
Quotation 3 Company Name
Quotation 4 Company Name
Quotation 5 Company Name
Winning Quotation Company Name

In the Past Purchase Order Table, this is just a text name of the company.

How do I query the Past Purchase Order Table to return ALL Records where a
Company Name has been used in one of the fields, ie:

Record 1 - Company 1 quoted (in Quotation 2 Company Name)
Record 2 - Company 1 quoted (in Quotation 4 Company Name) and won (Winning
Quotation Company Name)
Record 3 - No field has Company 1 in it.

The Query should return the information in Record 1 and 2, but not 3.

The default value for a Company Name field in the Purchase Order Table is
"None".

Yes, I know I could structure the database differently; however, the above
structure is the "best fit" for all the other reports and information that is
to be collated and actioned. Also, I have trying to minimise the database to
tables and queries where possible, to reduce maintenance issues with broken
code and/or macros.

I thank you all for any help.

Cheers

 

[Jim]

This seems pretty straight forward, so I'm probably missing something! Why
can't you just OR the six conditions in a where clause? That would return a
set of past records where at least one of the quotation fields has the
company name. Is this not what you're after?
jim

 

[John W. Vinson]

OK, let me explain the subject a little better. Please note, I have not yet
built the query, I am still working on the "how to" part of it.

I have a table that stores Purchase Order information. This is a LARGE table
and contains all the relevant information of one Purchase Order. It is
essentially the "End Product" of the entire Database (its reason for being).

If you're jamming data from multiple tables into your LARGE ("wide" I presume)
table in order to produce a Purchase Order output, *you're on the wrong
track*. It's not necessary to have One Master Big Table to contain all the
data for a database, in fact it's a complete reversal of the principles of
relational database design! Each table should contain a limited amount of data
about a specific type of entity (Customer, Supplier, Product, ...); when you
need to see data about multiple entities onscreen or on paper together, you
would use a Query joining these tables.

The table is the main driver to support financial management, data tracking
and to - pertinant for this question - review all suppliers for past
performance. As such, the table includes the Purchase Order Decision
information and quotation information.

I also have a table that is a list of all the Companies/Suppliers we deal
with. Nice and simple table, nothing out of the ordinary. Oh, and this table
has the Primary Key as the Company Name. This has been set after MUCH
consideration of the naming convenstions for companies for us. What it means,
for this problem, is that the Company Name in the Purchase Order Table
(selected by drop down) is a WYSIWYG data field.

It's perfectly possible (and indeed preferable) to have a dropdown (ON A FORM,
not in a table!) which stores a meaningless, numeric ID while it displays a
human-meaningful text supplier name.

In order to "cross reference" and pull up all the records in the Purchase
Order table that relate to a specific company I use a Form with a Sub Form
(the Form is based on the Company Table, while the Sub-Form is the Purchase
Order Table). This allows personnel to review previous company performance
FOR THE CURRENT FINANCIAL YEAR.

This is where the problem starts. At the end of each FY, the information in
the Purchase Order Table is ammended to another table (called Past Purchase
Order Information). All the Company Names are transferred to the Past
Purchase Order Table and the Past Purchase Order Table does not have any
relationships with anything - it is just a data table for datamining. The
Purchase Order Table is then reset for the new Financial Year.

You're using two tables to do the work of one. You could have *JUST* a table
of all purchase orders (past and current) and use a Query selecting by date
range to get this year's, or last year's, or 2003's POs. If you have a few
hundred thousand POs per year Access can handle this just fine; if you have a
lot more you should be in SQL/Server anyway.

The Purchase Order, and Past Purchase Order tables uses the Company Name as
a drop down field in six seperate fields (each named differently in the
Purchase Order Table). These fields are:

Quotation 1 Company Name
Quotation 2 Company Name
Quotation 3 Company Name
Quotation 4 Company Name
Quotation 5 Company Name
Winning Quotation Company Name

And if you ever get a SIXTH quote, what will you do? Redesign your table,
redesign all your forms, redo all your reports, rewrite all your code?

You're "committing spreadsheet". If each PO has one or more quotes, you need
*two tables* in a one to many relationship; each quotation would be a new
record in this table, not a new field.

In the Past Purchase Order Table, this is just a text name of the company.

How do I query the Past Purchase Order Table to return ALL Records where a
Company Name has been used in one of the fields, ie:

Record 1 - Company 1 quoted (in Quotation 2 Company Name)
Record 2 - Company 1 quoted (in Quotation 4 Company Name) and won (Winning
Quotation Company Name)
Record 3 - No field has Company 1 in it.

You'll need a criterion of

[Enter company name:]

on each of the six fields, or (better) use a combo box on a Form selecting the
company names, and use

[Forms]![YourFormName]![cboCompanyName]

on each of the six fields, with

"None"

on the second line of the query grid under each of these six criteria.

The Query should return the information in Record 1 and 2, but not 3.

The default value for a Company Name field in the Purchase Order Table is
"None".

Yes, I know I could structure the database differently; however, the above
structure is the "best fit" for all the other reports and information that is
to be collated and actioned. Also, I have trying to minimise the database to
tables and queries where possible, to reduce maintenance issues with broken
code and/or macros.

Designing your tables to fit your reports *is a very bad idea*; the trouble
you're having is an excellent example!

 

[Nick]

Jim,

The below is EXACTLY what I want it to do. The real question, now, is -
which field do I apply the WHERE criteria to? I will make the assumption that
the criteria is applied to the Past FY PO Table primary key, and then this
should limit the records to those that include the company name.

I will give the above a go and see what happens.

Cheers
--

Nick B

This seems pretty straight forward, so I'm probably missing something! Why
can't you just OR the six conditions in a where clause? That would return a
set of past records where at least one of the quotation fields has the
company name. Is this not what you're after?
jim

 

[Nick]

John,

Thank you and I understand your points. As background to assist in
understanding the context of the database let me explain:

I am not a programmer or a Software Engineer, nor the Consultant for the
organisation. I am a Civil Engineer (with a background in strategic project
management and systems engineering/dynamics) who is developing the database
as an in-house development (and addition to my normal work) for an
organisation that is separated from any reach back capability to our higher
group. The database must be structured to collect and store relevant
information based on what we internally need to check and track and what we
and our highers require for financial and performance management. This is
what I meant by "Best Fit". The database is not designed to produce our
reports per se, it is designed to collect, store and control the information
that we must track and report on for project maintenance, project estimation
and financial/performance management.

Though I agree that many aspects of the database can be designed a lot
better, the database has been structured to meet current organisational
tracking system. Though I am a strong advocate of positive organisational
change (and believe that in most cases an IT, policy, procedure or other
intervention should produce such change), I have also seen many cases of
where IT systems have been introduced without the proper consideration of the
organisation, its structure and procedures. This has often led to the
organisation having to adapt to the IT system, as opposed to the IT system
supporting and serving the organisation.

Given that the two key aspects of the database (Project Information and PO
Management) are constrained by "numbering conventions" outside of the control
of the organisation, and given to change them would require a significant
change to how our higher does business (though would stream line a lot of
tracking and data mining issues - supporting financial and performance
management), the reality is that this will not occur for multiple reasons.

It is within this context that the database has been developed.

The two key constraints on the database are the PO numbering system and the
Project Numbering system.

The PO Numbering System is a unique number by financial year. Every
financial year, the numbering system reverts back to 1 (ie, the first PO of
the new FY is 00001-FY08/09). Though I would love to store all PO data in one
table (making a lot of things easier and meaning that contractor and
procurement decision information can be stored in related tables), I can not,
for the life of me, yet find a way to have a unique numbering system that
meets the requirement and STORE that number in the PO Table. This is why I
have gone down the road of two tables - to meet organisational requirements
and to maintain the numbering system to be relevant for the current FY.

The second issue is Project Numbers. Project Information is stored in
another, related, table. Project Numbers are, once again, unique for the FY.
Higher issues these and will re-use them. Projects only go for a FY - even
when a project is over two or three FYs it is issued a new Project Number
each FY. Why? Simplicity - since about 95% of the Higher's projects (to which
we are a chuck) are FY only projects. What it means is that Project Numbers
are reused each FY and may not link with last FY projects. As such, I have to
manage this differently to a one table solution (though I could use a primary
key made up of Project Number and FY). So at the end of each FY, all project
information is amended to a Past Project Information Table and then deleted
ready for the new projects and project numbers (of which about 90% will be
new items, or iterations of old items with different start up data).

This is why there are two tables that essentially do the same thing, and why
the PO table is (as you correctly pointed out) so wide. Though it would be
better to have all procurement and quotation information in one table, and PO
information in a related table (and use a Sub-Form on the PO Form for
quotations), my level of capability and the organisational procedures are not
conducive to this.

With respect to 5 quotations (no more, no less)? This has been set due to
the system architecture, as highlighted above. The fact that the PO
Information is deleted from the primary table each FY means that having a
table for quotations would become useless as the relationships would never
marry up. The “magical†number five has come from review of current data and
a risk assessment that identifies that the risk of having more than five is
low and manageable within the context of the organisation’s requirements.

I used the information you provided, as it is similar to some other things I
have done, but that did not quite spit out the correct information.

Thank you for your comments. You are correct in many of the aspects you
presented, and I will consider the structure of the database as a future
version possibility while sorting out the capability of the current version.

Luckily, this is still all in the development stage!

--

Nick B

OK, let me explain the subject a little better. Please note, I have not yet
built the query, I am still working on the "how to" part of it.

I have a table that stores Purchase Order information. This is a LARGE table
and contains all the relevant information of one Purchase Order. It is
essentially the "End Product" of the entire Database (its reason for being).

If you're jamming data from multiple tables into your LARGE ("wide" I presume)
table in order to produce a Purchase Order output, *you're on the wrong
track*. It's not necessary to have One Master Big Table to contain all the
data for a database, in fact it's a complete reversal of the principles of
relational database design! Each table should contain a limited amount of data
about a specific type of entity (Customer, Supplier, Product, ...); when you
need to see data about multiple entities onscreen or on paper together, you
would use a Query joining these tables.

The table is the main driver to support financial management, data tracking
and to - pertinant for this question - review all suppliers for past
performance. As such, the table includes the Purchase Order Decision
information and quotation information.

I also have a table that is a list of all the Companies/Suppliers we deal
with. Nice and simple table, nothing out of the ordinary. Oh, and this table
has the Primary Key as the Company Name. This has been set after MUCH
consideration of the naming convenstions for companies for us. What it means,
for this problem, is that the Company Name in the Purchase Order Table
(selected by drop down) is a WYSIWYG data field.

It's perfectly possible (and indeed preferable) to have a dropdown (ON A FORM,
not in a table!) which stores a meaningless, numeric ID while it displays a
human-meaningful text supplier name.

In order to "cross reference" and pull up all the records in the Purchase
Order table that relate to a specific company I use a Form with a Sub Form
(the Form is based on the Company Table, while the Sub-Form is the Purchase
Order Table). This allows personnel to review previous company performance
FOR THE CURRENT FINANCIAL YEAR.

This is where the problem starts. At the end of each FY, the information in
the Purchase Order Table is ammended to another table (called Past Purchase
Order Information). All the Company Names are transferred to the Past
Purchase Order Table and the Past Purchase Order Table does not have any
relationships with anything - it is just a data table for datamining. The
Purchase Order Table is then reset for the new Financial Year.

You're using two tables to do the work of one. You could have *JUST* a table
of all purchase orders (past and current) and use a Query selecting by date
range to get this year's, or last year's, or 2003's POs. If you have a few
hundred thousand POs per year Access can handle this just fine; if you have a
lot more you should be in SQL/Server anyway.

The Purchase Order, and Past Purchase Order tables uses the Company Name as
a drop down field in six seperate fields (each named differently in the
Purchase Order Table). These fields are:

Quotation 1 Company Name
Quotation 2 Company Name
Quotation 3 Company Name
Quotation 4 Company Name
Quotation 5 Company Name
Winning Quotation Company Name

And if you ever get a SIXTH quote, what will you do? Redesign your table,
redesign all your forms, redo all your reports, rewrite all your code?

You're "committing spreadsheet". If each PO has one or more quotes, you need
*two tables* in a one to many relationship; each quotation would be a new
record in this table, not a new field.

In the Past Purchase Order Table, this is just a text name of the company.

How do I query the Past Purchase Order Table to return ALL Records where a
Company Name has been used in one of the fields, ie:

Record 1 - Company 1 quoted (in Quotation 2 Company Name)
Record 2 - Company 1 quoted (in Quotation 4 Company Name) and won (Winning
Quotation Company Name)
Record 3 - No field has Company 1 in it.

You'll need a criterion of

[Enter company name:]

on each of the six fields, or (better) use a combo box on a Form selecting the
company names, and use

[Forms]![YourFormName]![cboCompanyName]

on each of the six fields, with

"None"

on the second line of the query grid under each of these six criteria.

The Query should return the information in Record 1 and 2, but not 3.

The default value for a Company Name field in the Purchase Order Table is
"None".

Yes, I know I could structure the database differently; however, the above
structure is the "best fit" for all the other reports and information that is
to be collated and actioned. Also, I have trying to minimise the database to
tables and queries where possible, to reduce maintenance issues with broken
code and/or macros.

Designing your tables to fit your reports *is a very bad idea*; the trouble
you're having is an excellent example!

 

[Nick]

OK

That worked.

I applied the WHERE to the Primary Key, and then turned off "Show".

Thanks a lot. Now to the next problem!

Final SQL:

SELECT [Past FY Purchase Orders].[PO Number], [Past FY Purchase
Orders].[Date Raised], [Past FY Purchase Orders].[Contractor 1 Name], [Past
FY Purchase Orders].[Contractor 2 Name], [Past FY Purchase
Orders].[Contractor 3 Name], [Past FY Purchase Orders].[Contractor 4 Name],
[Past FY Purchase Orders].[Contractor 5 Name], [Past FY Purchase
Orders].[Winning Contractor], [Past FY Purchase Orders].[Financial Year],
[Past FY Purchase Orders].[PAS Number], [Past FY Purchase Orders].[Cost
Account], [Past FY Purchase Orders].[Procurement Description], [Past FY
Purchase Orders].[Specification Reference], [Past FY Purchase Orders].[ADS
RFQ-RTF Reference], [Past FY Purchase Orders].[Contractor 1 Compliant], [Past
FY Purchase Orders].[Contractor 1 Quote Reference], [Past FY Purchase
Orders].[Contractor 1 Quote Recieved Date], [Past FY Purchase
Orders].[Contractor 1 Quote Cost], [Past FY Purchase Orders].[Contractor 2
Compliant], [Past FY Purchase Orders].[Contractor 2 Quote Reference], [Past
FY Purchase Orders].[Contractor 2 Quote Recieved Date], [Past FY Purchase
Orders].[Contractor 2 Quote Cost], [Past FY Purchase Orders].[Contractor 3
Compliant], [Past FY Purchase Orders].[Contractor 3 Quote Reference], [Past
FY Purchase Orders].[Contractor 3 Quote Recieved Date], [Past FY Purchase
Orders].[Contractor 3 Quote Cost], [Past FY Purchase Orders].[Contractor 4
Compliant], [Past FY Purchase Orders].[Contractor 4 Quote Reference], [Past
FY Purchase Orders].[Contractor 4 Quote Recieved Date], [Past FY Purchase
Orders].[Contractor 4 Quote Cost], [Past FY Purchase Orders].[Contractor 5
Compliant], [Past FY Purchase Orders].[Contractor 5 Quote Reference], [Past
FY Purchase Orders].[Contractor 5 Quote Recieved Date], [Past FY Purchase
Orders].[Contractor 5 Quote Cost], [Past FY Purchase Orders].[Quotation
Comments], [Past FY Purchase Orders].[Quotation Comments], [Past FY Purchase
Orders].[PDR Comments], [Past FY Purchase Orders].[Contract Reference
Number], [Past FY Purchase Orders].[Final Price], [Past FY Purchase
Orders].[Is the Cost in AUD], [Past FY Purchase Orders].[Reason for Price
Variation], [Past FY Purchase Orders].[Comments on Works/Supply], [Past FY
Purchase Orders].[Use Contractor Again]
FROM [Past FY Purchase Orders]
WHERE ((([Past FY Purchase Orders]![Contractor 1 Name])=[Forms]![Company
History Complete]![CompanyName])) OR ((([Past FY Purchase Orders]![Contractor
2 Name])=[Forms]![Company History Complete]![CompanyName])) OR ((([Past FY
Purchase Orders]![Contractor 3 Name])=[Forms]![Company History
Complete]![CompanyName])) OR ((([Past FY Purchase Orders]![Contractor 4
Name])=[Forms]![Company History Complete]![CompanyName])) OR ((([Past FY
Purchase Orders]![Contractor 5 Name])=[Forms]![Company History
Complete]![CompanyName])) OR ((([Past FY Purchase Orders]![Winning
Contractor])=[Forms]![Company History Complete]![CompanyName]))
ORDER BY [Past FY Purchase Orders].[Date Raised];



--

Nick B

Jim,

The below is EXACTLY what I want it to do. The real question, now, is -
which field do I apply the WHERE criteria to? I will make the assumption that
the criteria is applied to the Past FY PO Table primary key, and then this
should limit the records to those that include the company name.

I will give the above a go and see what happens.

Cheers