Looping through fields

[hoofit]

Happy holidays all,

Part got cut off. I have the following code to loop thru fields in a table.

Do While rstIntervalValues!IntervalValue = rstINTERVALS!IntervalValue. I need to modify this to include another field like;

Do While rstIntervalValues!IntervalValue AND CATEGORYCODE = rstINTERVALS!IntervalValue AND CATEGORYCODE.

So I am comparing 2 fields from 2 tables. Can someone assist with the syntax?

Thank you in advance

hoof

 

[TheAceMan1]

How are ya hoofit . . .

Its hard too tell the intent of your logic here. Perhaps you can explain it in more detail ... aka ... what result are you expecting?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hoofit]

AceMan1

This code...
Do While rstIntervalValues!IntervalValue = rstINTERVALS!IntervalValue evaluates values of 2 fields in 2 recordsets. The fields are not, by themselves, unique. Thus, I could end up with an IntervalValue of 500 in one recordset field and 500 in the other recordset field. So, what I want to do is include a second field from each recordset to make a unique combination. Thus, the comparison would look something like this;

Do While rstIntervalValues!IntervalValue AND CATEGORYCODE = rstINTERVALS!IntervalValue AND CATEGORYCODE

Here I am using 2 fields from each recordset, IntervalValue and CategoryCode. This arrangement makes up a primary key. I just don't know how to combine the 2.

hoof

 

[TheAceMan1]

hoofit . . .

I understand what your after. [purple]I just don't see the point. [/purple] Are you trying to [blue]make your own unique primary key[/blue] or what? Besides ... I doubt [blue]CATEGORYCODE[/blue] could provide enough values to prescribe unique. Either [blue]IntervalValue[/blue] or [blue]CATEGORYCODE[/blue] has to be unique to begin with.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I do not get it, but it sounds as simple as just building a query linking the two fields in table one to the two fields in table two. Then either do an update query or use this query as the sql for the recordset.

BTW the proper terminology is a "Composite primary key". This means two fields that together form a primary key.

 

[hoofit]

I was able to work this out.

If rstIntervalValues!IntervalValue & CategoryCode = rstINTERVALS!IntervalValue & CategoryCode Then.....

Yes this is a composite primary key. Neither field by itself is unigue, rather the combination of fields makes it unique. Ended up being rather simple. I have a tough time putting thoughts into words.

Thank you much

hoof

 

[TheAceMan1]

hoofit . . .

Glad you've reached resolution. However ... I still don't think [blue]CategoryCode[/blue] can provide enough values to support the uniqueness required.

BTW: more than one field used as a primay key is also known as a [blue]compound key[/blue] ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hoofit]

AceMAn1,
Just a followup. Let's say you were in charge of aircraft maintenance. Your job is to set up a maintenance schedule covering both long and short term service. To tackle the job, you break down the task into manageable pieces by categorizing the various services you provide like oil changes, routine inspections, hydraulic system inspections etc. Each of the services falls into a category and is performed at different times or frequencies. Say every 10 hours do this, every 100 hours do that and so on. You may end up with 20 frequencies. Each one of these frequencies is a "category" and each frequency is completed within a specific period of time - the "Value". So, neither the frequency nor the value is unique. But the frequency AND the value are unique. Like this....

Maintenance a, Frequency 1
Maintenance a, Frequency 2
Maintenance a, Frequency 3

Maintenance b, Frequency 1
Maintenance b, Frequency 2
Maintenance b, Frequency 3

Thus, the importance of each field and the necessity of the composite primary key.

hoof

 

[TheAceMan1]

hoofit . . .

Well fancy that. TheAceMan1 is US Airforce veteran [blue]1st lieutenant[/blue] ... assigned to electronic maintainance. Were talking the old f4 fighters here! I can't give you a full job description but I fully understand your last post.

As long as uniqueness of your primarykey satisifies you ... [blue]then good enough![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[hoofit]

What are the odds.....? Well, thank you for what you do for our country. I think your job description would take up more space (and time) than we have here.

hoof