Combo boxes and dlookup help 1

[JBuckley2000]

Hey everyone,

I am wanting to see if maybe you guys could help me out--I am creating an input table from scratch...I converted an old dbase3 file to Access 2.0 and now I have about 90000 records of info from old reports. What I am needing to do now is creat a new input table (which I have done), but there is a certain part of it where I don't want to have to add information because I have 2 fields which one stand for the ID number, while the other gives the word description of the ID number. I am wanting to be able to pick from a combo box the ID code (which I have done) and then the description appears on the record table along with the ID code. Using the wizard, I see, I can only make only one appear...how can I make both come up in the record table? I have tried using Dlookup, but that only makes the description come up on the input table...does any of this make sense? I think this is really simple, but I need help.

Thanks for reading!!!

Jason Facey

 

[Robertd]

Hi Jason,

Ok there are a few things i'm not sure of from your request.

the table you are entering data into obviously has a field for the IDNumber, that is you are adding records to this table based on records from another table. if this is the case then you don't need to add the description to the table you are adding. to make the system work they way you want it to base the entry on a query include the table that you want to add records to and the table that has the IDNumber and description. include the IDNumber from the table you want to update and the description from the other table. for the relationship between the tables in the query set it to include all records from table1 and only those records from table2 where the join fields match in this case it will be IDNumber. Now the recordset created by this will display the table1 data as well have the description you could then use this as a data source for a form or report etc.

HTH

Robert Dwyer
rdwyer@orion-online.com.au

 

[JBuckley2000]

Hey Robert,

I just got back to the office after being out a few days and thanks a ton for your reply. I have not had a chance to check and see if this works, but it sounds very feasible as of now--yeah, I am trying to create the input table from a list of records that already exist. I did create another table which lists the ID number and the description for each. I will try setting up the query you have described. Hoepfully it will work--thanks a ton for the help!

Jason

 

[JBuckley2000]

Hey Robert,

IT WORKS AWESOME!!! Well, almost. I can't believe that is all it took. I did create the query, but for some reason, not all of the data transferred (I only got 26000 of the 96000 records in the new query table). I am thinking it did not transfer all of the records since not all of the record lines had something under the IDNumber because it did not transfer right from dbase3. In the past, users had to add a "Y" under a field, and where ever the Y was, a number would be pasted under the IDNumber column. How would I be able to get rid of the "Y's" and have numbers in the IDNUmber instead? This is my last problem and it is looking damn good. Thanks a ton and I hope you can finish helping me out!

Jason Facey

 

[Robertd]

Hi Jayson,

did the two tables start from from one common table, ie what links the two tables you have. I thought it was that the IDNumbers in one table linked the IDNumbers in the other.

How are the two tables linked??
to change the "Y" to an IDNumber you will need to run an update query, this may not be straight forward.

can you give us some more information regarding the structure and fields.




Robert Dwyer
rdwyer@orion-online.com.au

 

[JBuckley2000]

Hey Robert,

Yes, I have 2 tables that are connected through both having an IDNumber. To be a little more specific, my record table that I converted from dbase3 is a table that keeps track of all of the return products we have at our plant...it has information such as date when it came in, employee who looked over it, what product it was, and most importantly, the defect code (my IDNumber) which is why the product was sent back. In the dbase3 version, users would have 13 fields to choose from in which they would place a "Y" under the reason for why the defect failed. The program would then place a code number (1-13) for whichever field had a "Y" in it. Right now, I have a lot of "Y's" which didn't process through the old database correctly and I want to get rid of all of those columns, and just have a field for the code. I hope I explain it this time. The second table has 2 fields---the Defect Code and the Defect Description, because I have been wanting to also have the word description in the records which a user chooses a code. Thanks for checking this out and I can see where this might be too much to look at.

Jason

 

[Robertd]

Hi Jayson,

sory i'm still a little confused but,...
from the origional defect table for each record there will only be one "defect type" column with a "Y" in it.
that is one of thirteen columns will have a "y"

ok i would be making a backup copy of the data base before modifying the data. ;-)

easiest way to do this is with update query (13 of them)for each set the
criteria to not is null so only the records from the origional table with a "y" in this column are selected
"update to" to your returned product table defect code field with the defect IDNumber number

one query for each of the columns that identify the old defect Type.

the idea is for each row, if the column has a "y" put a defect number in the new returned products table defect field.
you may need to change the criteria part as i'm not sure if this would come across as a text "y" or Yes/No field.

this way i think what you want to end up with is a table that has the defect ID number and the description,
a table for the returned items that has the defect ID number (this would be linked to the defect table by the defect ID number field)

the only thing that i'm not sure of is how the row for the returned product is identified, in other words how to link each record.you would have maybe some sort of sequence number i presume that would identify each returned product record.

to check the queries before updating the table you can choose the "data sheet" display whilst in query design.
(i think you can do this in access 2) and view the records that will be updated

also the update could be done as one query but at the end of the day a bunch of simpler ones for this one of exercise is easier.

maybe this has given you some ideas
let us know
HTH

Robert Dwyer
rdwyer@orion-online.com.au

 

[JBuckley2000]

Hey Robert,

Well my prob is I am not used to writing code. For the query, I don't understand how I am going to be able to create a query which will do such a thing and update the records. What you are suggesting is exactly what I need and all of the details are correct. For each record, we have the date it was entered, who checked it, the product, when it was made, defects that are possible (several different fields which should only have one field per record with a "Y" in it), and specific comments. The defect code column does exist in this table , but not all of the records converted over correctly and some left out the code, but have the "Y". This is the record table. The other table had the defect code and defect description. If this seems like too much for me to learn over the forum I understand, but I am willing to try anything.
Thanks,

J

 

[Robertd]

Hi Jayson,

Ok for starters make a copy of the database (so you can play with, and not affect the inuse data)

goto the query tab of the (copy) database and make a "new" query select the table for the query then ok (you've done this already) then select the field you want to view (this is a select query for the time being).

add say the product ID and say only the first "defect" Y column. for the defect column put a "y" in the criteria grid then run the query. check that the result gives only the records that have a "y" (include the inverted comma's)in this field. if the result agrees with the expected records then we know the defect fields did come accross as a text field. you could also check this by looking at the table in design view.

if this is the case you can proceed to the next step change the query to an Update query (the little box in the tool bar with the pencil about in the midle of the screen) the query grid will change ie an extra row will appear add your IDNumber your new fault number id field by double clicking the field in the table box. in the query grid for this field put your "defect ID number" for this specific type of defect ie if this represents defect 1 then put the number in the "update to" field in the query grid. you might also like to add the criteria Is Null to the criteria field for you "defect" IDNumber field this will not update the records that already have an entry for the "defect" IDNumber field.

from here you could check the records that will be updated by selecting the datasheet view third button from the left in query design menu bar. if you are happy with the records that will be modified then click the run the query and all those records will be modified

the procedure for the other records will be the same after changing the old "y" field for the various "defects" change the "defect" IDNumber to suit the different fields and re-run the query. check the results if all went well you modified the backup database and now you are ready to modify the actual database but NOT before making another backup copy just in case ;-)

HTH

Robert Dwyer
rdwyer@orion-online.com.au

 

[JBuckley2000]

Hey Robert,

One thing to say first===WOO HOO!!!! That was awesome and you don't understand that is going to help me out with a couple of different parts of the table that I wanted to update!!! Thanks a ton for all that you have helped me with and I hope I wasn't too much of a moron for you. I really appreciate the help and how quick you have been in helping also. Good luck to you and I am sure I will be on here all of the time---I think I got it now!!!

Jason Facey

PS YOU ARE THE MAN!!