Access choose function in query field expression

[Coastie]

Good afternoon-

There is a table with the following fields:
PK*, Equipment_ID, Battery_Serial
*= arbitrary primary key

Each piece of equipment has between 0 and 3 batteries, and each battery has a non-sequential serial number. Unfortunately, this creates multiple records for each piece of equipment. I want a query that consolidates the information onto a single line for each piece of equipment. The fields I need are:
Equipment_ID, Battery_1_Serial, Battery_2_Serial, Battery_3_Serial

I don't care if the serial numbers are null for non-existent batteries.

I thought I was on the right track by using the Choose() function in the expression builder, but I can't develop the proper choices variable. I tried comparing the Equipment_ID from the table to the one in the query, but that was circular logic.

This is a medium priority item, as I don't need it until April 9th. Thank you.

Matt

[Rabbit]

Good afternoon-

There is a table with the following fields:
PK*, Equipment_ID, Battery_Serial
*= arbitrary primary key

Each piece of equipment has between 0 and 3 batteries, and each battery has a non-sequential serial number. Unfortunately, this creates multiple records for each piece of equipment. I want a query that consolidates the information onto a single line for each piece of equipment. The fields I need are:
Equipment_ID, Battery_1_Serial, Battery_2_Serial, Battery_3_Serial

I don't care if the serial numbers are null for non-existent batteries.

I thought I was on the right track by using the Choose() function in the expression builder, but I can't develop the proper choices variable. I tried comparing the Equipment_ID from the table to the one in the query, but that was circular logic.

This is a medium priority item, as I don't need it until April 9th. Thank you.

Matt

There's no field that tells us whether the serial is for battery 1, 2, or 3?

[Coastie]

There's no field that tells us whether the serial is for battery 1, 2, or 3?

Unfortunately no. I have no control over the structure/relationships of the database, so I am at the mercy of the architects.

I am guessing it has something to do with the fact that the number (1,2,3) of the battery doesn't usually matter.

Thank you for the quick response.

Matt

[Rabbit]

Well to get it that way I was thinking of a crosstab query but without a field to specify which battery it is, we'll have to create it. Is it safe to assume that battery serials are unique and that they won't be switched around to different equipment and that all the equipment will have at least 2 batteries?

[Coastie]

Well to get it that way I was thinking of a crosstab query but without a field to specify which battery it is, we'll have to create it. Is it safe to assume that battery serials are unique and that they won't be switched around to different equipment and that all the equipment will have at least 2 batteries?

Battery serials should be unique, however, I am not sure if the developers prevented duplicate values for the battery serial. Also, we can't guarantee that all equipment will have at least 2 batteries. Some have 1, most two and few 3.

Matt

[Coastie]

I made a query today to which I added a subdatasheet, and it displays a list of equipment, then the subdatasheet shows the list of battery serials for each piece of equipment. How does Access make that table, and can I get to its data?

Matt

[Rabbit]

I made a query today to which I added a subdatasheet, and it displays a list of equipment, then the subdatasheet shows the list of battery serials for each piece of equipment. How does Access make that table, and can I get to its data?

Matt

As far as I can tell, the subdatasheets are just mini queries. They won't, however, create new columns for you. For that you'll need a crosstab query. But a crosstab query will need a row heading (Equipment), a value (Battery Serial), and a column heading. The column heading will be a way to distinguish between battery serial 1, 2, and 3. And it's what you're missing. I was hoping to use a sub query to accomplish that but the requirements weren't there. So I'm thinking you might have to do it through code.

[Coastie]

I am continuing to dig on this one-

I found a field (in another table) that documents the total number of batteries for each Equipment_ID.

[Coastie]

So... have to do it through code... any hints to start me in the right direction?

I mean, all I really want to do it transpose the column of battery serials from the subdatasheet into a row. That's it! Oh well.

Matt

[Rabbit]

What you'll want to do first is to sort your table by equipment. And create a new field. Then you'll want to do something along the lines of the following: Open the recordset and store the equipment in a variable, check it against the prior equipment, if it's the same equipment, give the new field a value of i where i is the number of times it's come across the same equipment. Then you repeat till you loop through the whole recordset.

Just remember to reset i each time you see a new equipment.

Then afterwards you can use a crosstab query.

[Coastie]

I talked with my "customer," and they don't need the data stored in the horizontal format. They just would like it displayed horizontally in the report. Does that make my life easier or harder?

Matt

[Rabbit]

You can do:

Equipment
   - Battery Serial
   - Battery Serial
 
Equipment
  - Battery Serial
  - Battery Serial

But you won't be able to do:

Equipment - Battery Serial, Battery Serial, Battery Serial
Equipment - Battery Serial, Battery Serial, Battery Serial

Not unless you crosstab it.

[Coastie]

You can do:

Equipment
   - Battery Serial
   - Battery Serial
 
Equipment
  - Battery Serial
  - Battery Serial

But you won't be able to do:

Equipment - Battery Serial, Battery Serial, Battery Serial
Equipment - Battery Serial, Battery Serial, Battery Serial

Not unless you crosstab it.

Fair enough. Now, though, I am severely out of my element. Is that to be done in VBA? The table in question is an Oracle table that I have linked via ODBC, and my reports all reference queries to ensure the most up to date data. How do I incorporate my creation of a battery index into this scheme?

[Rabbit]

Fair enough. Now, though, I am severely out of my element. Is that to be done in VBA? The table in question is an Oracle table that I have linked via ODBC, and my reports all reference queries to ensure the most up to date data. How do I incorporate my creation of a battery index into this scheme?

Someone else will have to step in here. While I've used Oracle for simple queries, I've never linked it with Access so I don't know how that works. If you can interact with it the same way you can an Access table then I can help there.

My solution requires adding an extra column to that table. A one-time thing that you don't have to use code for.

[Coastie]

Someone else will have to step in here. While I've used Oracle for simple queries, I've never linked it with Access so I don't know how that works. If you can interact with it the same way you can an Access table then I can help there.

My solution requires adding an extra column to that table. A one-time thing that you don't have to use code for.

I don't have any local copies of any of the tables I am linked to. I can't modify the tables, but could create my own.

But, that is beside the point. I can have a local copy of the table in question, and can add the column for the battery index.

[Rabbit]

Requirements:

1) Table sorted by Equipment_ID

Assuming:

1) Your table is called Table1
2) The new field is called BatteryID

Public Function AssingID()
 
    Dim i As Byte
    Dim OldEquip As String
    Dim rst As Recordset
 
    i = 1
    Set rst = CurrentDb.OpenRecordset("Table1")
 
    With rst
        .MoveFirst
        OldEquip = !Equipment_ID
        .Edit
        !BatteryID = i
        .Update
        rst.MoveNext
 
        Do While Not .EOF
            If !Equipment_ID <> OldEquip Then
                i = 1
                OldEquip = !Equipment_ID
            Else
                i = i + 1
            End If
 
            .Edit
            !BatteryID = i
            .Update
            rst.MoveNext
        Loop
    End With
 
End Sub

I tested it and it works. Oddly enough I tested it unsorted and it still worked but I don't suggest it.

[Coastie]

THANK YOU!!!

I really appreciate the help. It works like a charm.

Now, I am adapting it to work with my linked table nonsense. In order to create the local table, I used an update query to pull the latest data into my local table. Then, following a run of the function you wrote, create the crosstab query.

Thanks again for all the help.

[Coastie]

In order to create the local copy of the table in question, I ran an update query. Is it possible to have that query run on startup, to ensure the local table has the correct data?

[Rabbit]

There's two things you can do, either through an autoexec macro that runs when you open a database, or a form that opens and runs code when you open the database.

[Rabbit]

THANK YOU!!!

I really appreciate the help. It works like a charm.

Now, I am adapting it to work with my linked table nonsense. In order to create the local table, I used an update query to pull the latest data into my local table. Then, following a run of the function you wrote, create the crosstab query.

Thanks again for all the help.

Not a problem.