Super Advanced Programming Question - making if statements on the fly 2

[blarson0]

20 stars to whomever can figure this one out ;-)

I have a table with assembly numbers and model codes, like this:

Assy Model Code
70360-RAB-02 AST35AFG2170ADFASDF324
34850-LRQ-04 HYLWP350JLGJEO70235GAS
72389-DAE-02 SDGHHPOEWYTHWETIPOPI67

These are just examples, the real data is proprietary...
each model code is 32 characters or so
There are hundreds of records like this.

Then I have a table of parts. It lists the part code, description, quantity, and the test for what model code it belongs to.

The test is in a format like this:
1. (15-2)
2. (15-1|15-2)
3. (12,13-B8&15-2&16,17-24)
and they get even more complex...
This is from a very old paradox database, which apparently used statements like this.

Anyway, what the tests mean is something like this:
1. If the model code has a 2 in the 15th place this part is included
2. If it has a 1 or a 2 in the 15th place this part is included
3. If the 12th and 13th places are B8 and the 15th is 2 and the 16th and 17th are 24 then include this part.

is there any way I can get this to work in access? I assume I will have to make a script to change it into legal 'if' statements, and then find some way to make a query that will use these if statements.

All I want to happen is for the user to type in an assembly number in my form, and it will show them what parts are in that assembly. The form is to view information only.

The parts table is over 500,000 records long, so making a script that loops through record-by-record for each search really isn't much of an option.

Any ideas or suggestions are more then welcome.

Thanks!

-Brad

 

[RottPaws]

I would use the mid function and create a dynamic WHERE clause.

I'm pressed for time, so I apologize in advance for typos, oversights, etc. . .

Do something like:

Dim strWHERE as string
Dim strSQL as string
dim str12_13 as string
Dim str15 as string
Dim str1617 as string


str1213=mid(txtASSY,12,2)
str15=mid(txtASSY,15,1)
str1617=mid(txtASSY,16,2)

'Edit the WHERE clause as needed
strWHERE="WHERE mid(Parts.PartNo,12,2)=" & str1213 & " AND mid(Parts.PartNo,15,1)=" & str15 & " and mid(Parts.PartNo,16,2)=" & str1617

strSQL="SELECT * FROM Parts " & strWHERE

'print the actual SQL in the debug window
debug.print strSQL

form!Parts.recordsourse=strsql

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[TrojanRabbit]

Don't forget ' in the WHERE clause. "WHERE ...='" & ... & "' ...='" & ....

 

[blarson0]

thanks for the help guys. I think I could write a script that would make the model code tests into the right WHERE format. But there is one problem...

The part numbers do not have any form of model code on them. here is a sample part record:

Part # Qty Desc. Test Code
70400-722 1 ROTATING KIT SUBASSY ((4,5-30|4,5-3A|4,5-3B|4,5-3C|4,5-3D|4,5-3K|4,5-3M|4,5-3P|4,5-3T|4,5-3U)&~20,21-0N)

That is one of the more advanced test codes. The way this is done, the parts tell which assembly they are in by the test code. I need to see what assembly has which parts, so basically use the test code in reverse. Tell the computer I need model code AGEIWO215TYEPQB25, and it will go through and tell me which part tests fit into there.

So if I can make a where statement based on (~20,21-0A &~20,21-0L&~20,21-0M), which should be possible, it would find which assemblies use that part. but I need to know which parts go into a specific assembly.

I'm sorry that this is so complicated. Do you understand what I need? Tell me if there is anything I should clarify.

Thanks!

-Brad

 

[RottPaws]

Hmmm.......If I understand this correctly, this could get really interesting, and may just be over my head. Ugh!

First, here's what I.....

The database is set up to do testing for a one-many relationship from the standpoint of a given part (one) may be used in multiple assemblies (many). Each part in the parts table has a field that lists it's search criteria to determine assemblies which have it as a part.

You need to test from the standpoint of a given assembly (one) has multiple parts (many).

So what you really have is a many-to-many relationship (a given part is used in many assemblies, and a given assembly uses many parts) but you don't have a junction table.

If my understanding above is correct, I've got only one idea for a possible solution and then I'll have to give up on the 20 stars.....

Is changing the database structure an option? If so, you could create a junction table and then write a script to loop through each part and create records in it that match up the parts to the assemblies. Then start it running and go away for a long weekend.

This would also require some plan to add new part/assy combinations in as needed in the future. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[blarson0]

actually, that is exactly what I was thinking about. I don't really like it as a solution, because I don't know how often the parts table is updated. But I think it might be my only choice.

I think that you understand the problem exaclty. I will talk to my employer about making a temporary table that could be updated every night or so.

Thanks for the help!

If anyone else has any ideas (that would avoid making a middle table) then feel free to try for those 20 stars ;-)

-Brad

 

[RottPaws]

How is the test code currently entered/modified? You may be able to set up something that automatically adds records to the junction table instead of entering the test code . . . ??? _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[blarson0]

Yeah that is part of the problem. All the tables are stored on a huge oracle database in the cooperate headquarters at who-knows-where. And, because they have to hire not-to-bright oracle programmers who couldn't care less about the individual plants, you have to download all the data as text reports. I can't even link the tables into access. So, we can download a report each night, which will take about 2 hours or so for oracle to sort through and make into a text file. Then download it, and run it through monarch which will make it into an access table. Then have my script sort through it to make our 'middle' table. Not a big deal, but once I'm gone I don't want to think about the secretaries trying to do this.

So, until the oracle programmers wake up, we will have to run this script each night.

-Brad

 

[Quehay]

Forgive me butting in, since you guys are obviously working hard here already. This is a real teaser of a problem.

Looking at the three sample tests that you had...if I understand correctly...you could code these with a SQL WHERE clause with LIKE and wildcards.

Tests:1. If the model code has a 2 in the 15th place this part is included
2. If it has a 1 or a 2 in the 15th place this part is included
3. If the 12th and 13th places are B8 and the 15th is 2 and the 16th and 17th are 24 then include this part.


For example (sloppy coding--it's just the idea):
[tt]WHERE Field (LIKE "%%%%%%%%%%1*&quot OR (LIKE "%%%%%%%%2*&quot
OR (LIKE "%%%%%%%%%%B2*&quot[/tt] etc.

Access may just lay down and croak if you try to run this, but theoretically, unless it's too late and I'm too half-baked, it should work... Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[RottPaws]

If you have to rebuild your database every day from a text file, my first thought is forget it. Unfortunately, I know that is often not an option once your fearless leader gets an idea (See Dilbert.com). Try to automate as much as you can.

After building and populating the database the first time, I'd try setting up a macro or code procedure that would bump up the database and the new text file against each other to find things that don't match and then process those in the database rather than trying to rebuild the entire database each night.

If you've got 1/2 million records in one table alone, Access is probably not the best choice for whatever you are trying to do anyway.

Good luck. _________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[wkendrvr]

"Access may just lay down and croak if you try to run this, but theoretically, unless it's too late and I'm too half-baked, it should work... "

It most likely will go into la-la land... Especially if this is a large database. might have some luck with the mid if you parse first, then try to find relationships...

 

[grtammi]

Brad:

Could you send 2 or 3 records of that table to me at gtammi@atsbell.com? I have an idea which I think might work, but I need to see the records first.

Thanks

Greg

 

[FoxProProgrammer]

Man, what a mess! Whoever designed the scheme in the old Paradox database or wherever it came from should be banned from designing any databases in the future. LOL

It seems to me that you need to parse the test code and build a Where clause like Rott suggested in the beginning of this thread. The parse routine is the most difficult part of this because of the way the data is encrypted.

First, you need to identify the key characters in the test code. Based on the examples that you provided, it looks like the key characters are:

(
)
~
-
,
|
&

If there are more, please provide them, along with a description of what they mean.

Here's how I would parse the test code.

Start at the beginning of the string. It begins with the ( key character.

Accumulate characters one at a time until you reach the next key character. Let's call the portion of the string between the ( and the next key character "first". "first" represents the position in the model code to look for data.

You now need to look for the next key character, which based on your sample data could be either a dash or a comma.

a. If you reach the - key character next, read one character at a time until you reach the next key character. Let's call the portion of the string between the second and third key characters "second". This is the data that you need to look for in the position represented by "first".

b. If you reach the , key character next, read characters one character at a time until you reach the next key character. Question: Can a test code have 12,13,14-B8, or can you only have two numbers before the dash? If the number of characters separated by the comma key character is fixed at 2, this is a *little* easier. If it isn't fixed, you will have to put this part in a loop until you find a dash. That just complicates it more.

| means Or the previous code with the code that follows.

& means And the previous code with the code that follows.

Ok, so this isn't a complete solution, but I think it gives you a glimpse into what is involved. It's a mess! If you have the Paradox program or whatever the database was originally written in, you might try to convert that code to Visual Basic.

If you think the parse routine discussed here is heading in the right direction, I'll be glad to try to help you sort it out, but you'll need to provide all the key characters and what they mean.

Best of luck. You're gonna need it! ) I got a headache just trying to figure this much out. LOL dz
dzaccess@yahoo.com

 

[blarson0]

Sorry I took so long to respond...

I have been working on a script to parse the code into conditional where statements, and making quite a bit of progress, when my employer found a report in oracle that will do it for you. These oracle programmers have made over 400 reports, and just thrown them all together, so you have to guess at which report will tell you what. One of the most common phrases around the office is "Dang Oricle!". It is too bad, because oricle is so much more powerful then access.

Anyways, everything is working out now. Thanks for the help, I am sure somebody can get 20 stars the next time I have such a big problem to sort through!

Thanks!

-Brad