469,352 Members | 1,945 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Access choose function in query field expression

14
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
Apr 5 '07 #1
19 6602
Rabbit
12,516 Expert Mod 8TB
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?
Apr 5 '07 #2
Coastie
14
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
Apr 5 '07 #3
Rabbit
12,516 Expert Mod 8TB
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?
Apr 5 '07 #4
Coastie
14
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
Apr 6 '07 #5
Coastie
14
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
Apr 6 '07 #6
Rabbit
12,516 Expert Mod 8TB
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.
Apr 6 '07 #7
Coastie
14
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.
Apr 6 '07 #8
Coastie
14
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
Apr 6 '07 #9
Rabbit
12,516 Expert Mod 8TB
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.
Apr 6 '07 #10
Coastie
14
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
Apr 6 '07 #11
Rabbit
12,516 Expert Mod 8TB
You can do:
Expand|Select|Wrap|Line Numbers
  1. Equipment
  2.    - Battery Serial
  3.    - Battery Serial
  4.  
  5. Equipment
  6.   - Battery Serial
  7.   - Battery Serial
But you won't be able to do:
Expand|Select|Wrap|Line Numbers
  1. Equipment - Battery Serial, Battery Serial, Battery Serial
  2. Equipment - Battery Serial, Battery Serial, Battery Serial
Not unless you crosstab it.
Apr 6 '07 #12
Coastie
14
You can do:
Expand|Select|Wrap|Line Numbers
  1. Equipment
  2.    - Battery Serial
  3.    - Battery Serial
  4.  
  5. Equipment
  6.   - Battery Serial
  7.   - Battery Serial
But you won't be able to do:
Expand|Select|Wrap|Line Numbers
  1. Equipment - Battery Serial, Battery Serial, Battery Serial
  2. 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?
Apr 6 '07 #13
Rabbit
12,516 Expert Mod 8TB
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.
Apr 6 '07 #14
Coastie
14
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.
Apr 6 '07 #15
Rabbit
12,516 Expert Mod 8TB
Requirements:

1) Table sorted by Equipment_ID

Assuming:

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

Expand|Select|Wrap|Line Numbers
  1. Public Function AssingID()
  2.  
  3.     Dim i As Byte
  4.     Dim OldEquip As String
  5.     Dim rst As Recordset
  6.  
  7.     i = 1
  8.     Set rst = CurrentDb.OpenRecordset("Table1")
  9.  
  10.     With rst
  11.         .MoveFirst
  12.         OldEquip = !Equipment_ID
  13.         .Edit
  14.         !BatteryID = i
  15.         .Update
  16.         rst.MoveNext
  17.  
  18.         Do While Not .EOF
  19.             If !Equipment_ID <> OldEquip Then
  20.                 i = 1
  21.                 OldEquip = !Equipment_ID
  22.             Else
  23.                 i = i + 1
  24.             End If
  25.  
  26.             .Edit
  27.             !BatteryID = i
  28.             .Update
  29.             rst.MoveNext
  30.         Loop
  31.     End With
  32.  
  33. End Sub
I tested it and it works. Oddly enough I tested it unsorted and it still worked but I don't suggest it.
Apr 6 '07 #16
Coastie
14
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.
Apr 7 '07 #17
Coastie
14
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?
Apr 7 '07 #18
Rabbit
12,516 Expert Mod 8TB
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.
Apr 7 '07 #19
Rabbit
12,516 Expert Mod 8TB
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.
Apr 7 '07 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

52 posts views Thread by Neil | last post: by
12 posts views Thread by Steve | last post: by
3 posts views Thread by mnjkahn via AccessMonster.com | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.