Text Box Lookup - There has to be a better way!

[orange]

What the.... !?!?

Back to the subject at hand, I finally got the DLookUp to work. I got it (the syntax) right by trial and error... mucho trials and mucho errors before it worked.



The updated version of the test db is attached.



How might a "recordset" work?

So you have your options working. The combo for data entry, and one of the others for display. Now it's the same thing only for RH. Is this correct?

[oleBucky]

Now it's the same thing only for RH. Is this correct?

That is correct.

[oleBucky]

Maybe it will give you some ideas to allow you to move forward...

Your input is greatly appreciated. I will review your suggestions on the test db and see how it might apply to my working db.

I will post ASAP.

[oleBucky]

I've been following this thread and I think you are having trouble because your table structure is not normalized. When there are field names with incrementing numbers, it usually means someone has "committed spreadsheet"; ie the Access table is "short and fat" (like a spreadsheet), instead of "tall and narrow".

I haven't got a clue as to what you are doing, but I tried to follow as best as I could. (see the mdb).

I normalized the main table - I called it tblStats - and added a couple of tables and two forms.... sorry about the colors. The form that is displayed when the mdb is opened is my brief attempt to look like the picture you posted. The other form, "frmAddStats", is how I would add entries.

Maybe it will give you some ideas to allow you to move forward...

Your analysis is exactly correct. I originated the db from a rather large ss. I really like the idea of the tall, narrow table with the relationship to LHPNO, LHPRO, RHPNO and RHPRO. My question is: Is is possible to convert my existing db to this table structure?

BTW, after working out the DLookUp issue here, and the conditional formatting of a subform's backcolor on a different thread, the attached jpegs shows what I was going for as a hitting card form.

[ssanfu]

Your analysis is exactly correct. I originated the db from a rather large ss. I really like the idea of the tall, narrow table with the relationship to LHPNO, LHPRO, RHPNO and RHPRO. My question is: Is is possible to convert my existing db to this table structure?

BTW, after working out the DLookUp issue here, and the conditional formatting of a subform's backcolor on a different thread, the attached jpegs shows what I was going for as a hitting card form.

The last time I had anything to do with baseball was way back when I played little league and two seasons of "Babe Ruth league (?)". So I really don't have any idea of what you are doing.
But you should be able to convert it. After all, I used your info for Babe Ruth to create the example mdb. If there is only a little bit of data, you can enter it by hand. If there is a lot, you could write some VBA to convert it.

[oleBucky]

The last time I had anything to do with baseball was way back when I played little league and two seasons of "Babe Ruth league (?)". So I really don't have any idea of what you are doing.
But you should be able to convert it. After all, I used your info for Babe Ruth to create the example mdb. If there is only a little bit of data, you can enter it by hand. If there is a lot, you could write some VBA to convert it.

OK... good news and bad news.

The bad news first. I tried to convert the existing hitting table to "tall and skinny" using my rudimentary knowledge of VBA and your suggestions. Thank Goodness I was using a working version of the db. My code butchered the existing data. I restored my old un-normalized db and will have to live with it as is until I can find a better VBA reference to search for appropriate code and syntax.

Now the good news: The pitcher's data is very similar to the hitter's data and I had not yet worked with that data. As the orginal data is in a very large spreadsheet and being far more familiar with VBA for Excel, I was able to convert it into a "tall and skinny" table by doing it in Excel first, then importing the table to Access. Using your ideas the new table has seven fields and over 12,500 records. I'll see if I can use your ideas to reproduce pitching cards to my liking. I will post after material developments.

[ssanfu]

OK... good news and bad news.

The bad news first. I tried to convert the existing hitting table to "tall and skinny" using my rudimentary knowledge of VBA and your suggestions. Thank Goodness I was using a working version of the db. My code butchered the existing data. I restored my old un-normalized db and will have to live with it as is until I can find a better VBA reference to search for appropriate code and syntax.

Now the good news: The pitcher's data is very similar to the hitter's data and I had not yet worked with that data. As the orginal data is in a very large spreadsheet and being far more familiar with VBA for Excel, I was able to convert it into a "tall and skinny" table by doing it in Excel first, then importing the table to Access. Using your ideas the new table has seven fields and over 12,500 records. I'll see if I can use your ideas to reproduce pitching cards to my liking. I will post after material developments.

How close is the data for the hitting data and the pitcher's data. Can they be on one table?
Hopefully, you are using the supporting look up tables for both hitter's and pitcher's tables (if you haven't combined them).

[oleBucky]

How close is the data for the hitting data and the pitcher's data. Can they be on one table?

The data is very similar and with some additions to the supporting tables they could easily be one table. However, the hitting table is over three times as large as the pitching table. Without an automated process, I think the conversion, even using my pseudo-automated process for converting from the original ss, would be a daunting undertaking. I might give it a shot and then append the pitching table.

Hopefully, you are using the supporting look up tables for both hitter's and pitcher's tables (if you haven't combined them).

I think the answer is yes. The FKs for both hitting and pitching look to the same tables.

[ssanfu]

The data is very similar and with some additions to the supporting tables they could easily be one table. However, the hitting table is over three times as large as the pitching table. Without an automated process, I think the conversion, even using my pseudo-automated process for converting from the original ss, would be a daunting undertaking. I might give it a shot and then append the pitching table.

It shouldn't be too hard. A couple of loops - the outer loop to step through the records and the inner loop to step through the fields. A few lookups to get the PKs from the supporting lookup tables and an insert command.

I'll be on a trip starting 5/1.. If you like, I'll work up some code to do the conversion (it will give me something to do)... I would need some records in the current hitters table (50 - 100), the new table you have for the pitchers and the lookup tables. All forms, queries and reports should be removed to reduce the size. And I would need it in A2000 format. Compact/repair and Winzip it. PM me with your email and I will reply...

[ssanfu]

I merged the 4 hitter tables into one table - tblHitters. I didn't try to change any other table; without more discussion I am totally lost.

[oleBucky]

Excellent! Thanks so much. There is no way I could have figgered out the code to do that (in this decade).

...without more discussion I am totally lost.

I will give the table a look and see if there is some input I can provide. I'll post when I have something.

Thanks again!

[oleBucky]

I merged the 4 hitter tables into one table - tblHitters. I didn't try to change any other table; without more discussion I am totally lost.

OK, here goes...

I've posted your normalized db with the addition of my table with pitching data, tblCardData, in the hopes it might help clarify how I am using this db. I also added to your comments on the table design tblHitters then attempted to coordinate my comments on the tblCardData. My goal is to append tblCardData with the data from tblHitters, so, as you suggested, all my data, hitting and pitching, is on one, tall, skinny table.

I also posted a pdf of a Babe Ruth's 1919 hitting and pitching cards with my annotations in the hopes of further explaining how db data relates to actual card data.

Finally, if you can be bothered, the Ball Park Baseball website has good basic description of the game (don't worry, it's one page with lots of white). It might give you some insight on what I am attempting with the db for www game access for my league members.

I am in and out all week but will check the forum should you have any questions.

Harry