Finding matches in an array

[Michael]

I need to find the current value for a historical record.

It is tricky because I need to look in a field that has
multiple values.

This is what I want to do.

Current Data choice one (easy direct relationship)

Else

Current Data choice two(hard.. detailed below)

Else
Historical Data

I want to report the current price availble.
My sales table has item, customer, and history price.
Customer table has customer, contracts.
Prices table has customer, item, price.
Contract table has contract, item, price

The tricky part is is that the contract field in the
customer table has all the contracts that are currently
available to that customer in a sequetial array from left
to right. The number of contracts is variable from
customer to customer from 0 to 7.


example:
Contract
C1 C2 C8 C5

The pricing should be extracted so that
Contract = C1
if item is found in Contract table where contract = c1
Price
else
if item is found in contact table where contract = c2
price
else
if item is found in contact table where contract = c8
price

so on and so forth...

If there is a way .... Well what can I say maybe I will
get some rest this weekend.

Thanks in advance.!!!

 

[Allen Browne]

Re-reading your post, it sounds like you do understand relations, but you
have a non-atomic field? This field *must* be broken into a related table in
order to do anything worthwhile with your data.

Perhaps the idea is that a contract can go through several revisions with
different prices? If so, a Contract will need many entries over time, with
price, date, etc.

I had started trying to explain basic relational design so the rest of this
may not be relevent to you:

1. First thing is to ensure each table has a primary key. If not, open the
Customer table in design view, and add a field named CustomerID, field type
AutoNumber. Click the Key icon on the toobar to make it the primary key.
Save. Close.

2. Open the Contract table in design view.
Add the primary key field:
ContractID AutoNumber mark as primary key.

3. Still in design view of the Contract table, add another field named
CustomerID, type Number. In the lower pane, set:
Field Size: Long Integer
Default Value: (get rid of the zero).
Save and close.

4. From the Tools menu, choose Relationships. Add the Customer and Contract
tables to the view if they are not there. Drag Customer.CustomerID onto
Contract.CustomerID to create the relation. Check the box for "Referential
Integrity". Okay the dialog. Save and close the Relationships window.

 

[Michael]

I do understand relations, but my expertise is limited
beyond that.

This logic is used to select the appropriate current
price.

Right now I have 3 places a price can come from.
1) I call Customer/Item Price This table is simple it
contains customer#, item#, and price.
2) Contracts is the next choice. Contracts are a
collection of item prices. These contracts are then
associated with a customer in a sequence. A customer can
have up to 7 associated contracts.
3)Products table. This is where the standard price is
located and is the last item in the hiearchy.


What I am trying to do for reporting purposes is reflect
the current price that would be paid for an item rather
than some price that is associated with a historical
transaction.

So I query a record from the history table, first I want
to check the customer/item price table, if the customer
and the item in the history record match a record in the
customer/item price table then I want to return the price
value from that record, if not...

I then want to check the contract prices that are
available to that customer. Here lies my delima.
Curently my customer table has a single field called
CONTRACTS that contain the available contracts in
sequential order space separated.

I want to be able to loop through those contract"id's"
until I reach the end or find a match.

if a match is found obviously again return the price else
use the price that was in the original history.

I believe I could change the table structure and achieve
my goal. I am trying to see if there is anyway to
achieve this without changing the table structure.

I create csv files of the data from our unix box and
publish them for our web administrator to provide online
ordering information. I would rather not have to use
different csv files, currently I update them nightly and
if I can continue to use these files it would be very
nice. I would know the data we both have is exactly the
same. He is using SQL and PHP to "explode" the array of
values. I am not even close to being able to comprehend
that.

I sincerely appreciate your help.

I am aware that this is asking alot.

Thanks

 

[Allen Browne]

Hi Michael.

You have correctly identified that the source of the problem is the
non-relational structure, i.e. Customer.Contracts contains non-atomic items.
Sounds like you also understand that the best fix would be to break this
down into related tables.

If that is no possible, you could pass the field into a function that takes
a ParamArray argument, so you can loop from lbound to ubound and FindFirst
the matching record in a recordset of contracts. You may need to massage the
string with the Replace() function before passing it in so as the have a
suitably delimited ParamArray.