Hi everyone!
I'm sure my problem is pretty easy to solve but I've been working on it for a long and my my brain is not working correctly anymore.
I'm working with an AS400 and I mhave to do an "interface" in Access. It's like a little system to make a production plan. It must get information about part numbers, description, set where the part number belongs, quantity of part numbers that the set uses, stock number(this is a text value, it could be "re", "co", "pt", etc but I just use "co" and "re"), stock and production group.
To connect Access to the server I vinculated three tables: teils, strus and lgbs. To make things faster, I created a query and then used create tbale query to make a new table. Then did a form and added new fields on that table: daily use, prod. days, stock on hand and period days.
I did a form and it works right. But my problem is from the first query:
SELECT DISTINCT RHDBD_16_STRUS. STFIRM, RHDBD_16_STRUS. STKOMP, RHDBD_16_TEILS. TEBEZ1, RHDBD_16_STRUS. STBGNR, RHDBD_16_LGBS.L SLANR, RHDBD_16_TEILS. TEMAGR, RHDBD_16_LGBS.L SLGBE INTO PLANEACION
FROM RHDBD_16_LGBS, RHDBD_16_STRUS, RHDBD_16_TEILS
WHERE (((RHDBD_16_STR US.STFIRM)="5") And ((RHDBD_16_STRU S.STKOMP)=(RHDB D_16_TEILS.TETE NR) And (RHDBD_16_STRUS .STKOMP)=(RHDBD _16_LGBS.LSTENR )) And ((RHDBD_16_STRU S.STBGNR) Not Like "ES*" And (RHDBD_16_STRUS .STBGNR) Not Like "MS*") And ((RHDBD_16_LGBS .LSLANR)="RE") Or (RHDBD_16_LGBS. LSLANR)="CO") And ((RHDBD_16_TEIL S.TEMAGR)="ASOX ")
ORDER BY RHDBD_16_STRUS. STBGNR;
This one is from the vinculated tables, and I want to get in the same query one to get the count of part numbers by set. This is the query that does it:
select count(RHDBD_16_ STRUS.STKOMP) group by RHDBD_16_STRUS. STBGNR.
I couldn't find the way to do it, so I used a code like this:
strPalabrasegun da = STBGNR
Set prueBa = DBEngine.OpenDa tabase("C:\Docu ments and Settings\mzarat e\
Mis documentos\PRUE BA\PLANEACION_D E_LA_PRODUCCION _PEM.mdb")
strSQL = "SELECT * FROM PLANEACION where PLANEACION.STBG NR like '*" & strPalabrasegun da & "*'"
Set taBla = prueBa.OpenReco rdset(strSQL, dbOpenDynaset)
With taBla
.MoveLast
.MoveFirst 'Voy al primer y al último registros con el objetivo de contar los registros.
cuentapalabra = taBla.RecordCou nt
Texto26 = cuentapalabra
That does it fast, no problem if it keeps so.
But there's another problem, the fields "set" and "part number" could be the same in several entries (does it mean "registro") . I mean, the same combination part number-set could be in many entries. So, it could be fixed with a select distinct, but the fields stock number or stock make it change. I know it's the same but I can't find a way to show it once.
Now I'm trying to use a sum, but I'm having problems again:
SELECT Sum(PLANEACION. LSLGBE) AS SumaDeLSLGBE
FROM PLANEACION
GROUP BY PLANEACION.STBG NR;
This makes it, but the same problem: how do I get it in an only query or how do I use it for VB. Well, actually I have it in VB, but I don't know the way o show the result from the sql statement in a textbox.
As you can see I'm not fine and maybe I'm not in the right way, so if you can help me to get the three queries in one or to get the resulta from my query to a textbox.
Thanks for the time and I hope you have understood -because the trouble is a little confussing and my English is not good enouh-, if you didn't, please write to this e-mail:
**Email removed as per site rules**
Thank you again and have a nice -amazing- day!
I'm sure my problem is pretty easy to solve but I've been working on it for a long and my my brain is not working correctly anymore.
I'm working with an AS400 and I mhave to do an "interface" in Access. It's like a little system to make a production plan. It must get information about part numbers, description, set where the part number belongs, quantity of part numbers that the set uses, stock number(this is a text value, it could be "re", "co", "pt", etc but I just use "co" and "re"), stock and production group.
To connect Access to the server I vinculated three tables: teils, strus and lgbs. To make things faster, I created a query and then used create tbale query to make a new table. Then did a form and added new fields on that table: daily use, prod. days, stock on hand and period days.
I did a form and it works right. But my problem is from the first query:
SELECT DISTINCT RHDBD_16_STRUS. STFIRM, RHDBD_16_STRUS. STKOMP, RHDBD_16_TEILS. TEBEZ1, RHDBD_16_STRUS. STBGNR, RHDBD_16_LGBS.L SLANR, RHDBD_16_TEILS. TEMAGR, RHDBD_16_LGBS.L SLGBE INTO PLANEACION
FROM RHDBD_16_LGBS, RHDBD_16_STRUS, RHDBD_16_TEILS
WHERE (((RHDBD_16_STR US.STFIRM)="5") And ((RHDBD_16_STRU S.STKOMP)=(RHDB D_16_TEILS.TETE NR) And (RHDBD_16_STRUS .STKOMP)=(RHDBD _16_LGBS.LSTENR )) And ((RHDBD_16_STRU S.STBGNR) Not Like "ES*" And (RHDBD_16_STRUS .STBGNR) Not Like "MS*") And ((RHDBD_16_LGBS .LSLANR)="RE") Or (RHDBD_16_LGBS. LSLANR)="CO") And ((RHDBD_16_TEIL S.TEMAGR)="ASOX ")
ORDER BY RHDBD_16_STRUS. STBGNR;
This one is from the vinculated tables, and I want to get in the same query one to get the count of part numbers by set. This is the query that does it:
select count(RHDBD_16_ STRUS.STKOMP) group by RHDBD_16_STRUS. STBGNR.
I couldn't find the way to do it, so I used a code like this:
strPalabrasegun da = STBGNR
Set prueBa = DBEngine.OpenDa tabase("C:\Docu ments and Settings\mzarat e\
Mis documentos\PRUE BA\PLANEACION_D E_LA_PRODUCCION _PEM.mdb")
strSQL = "SELECT * FROM PLANEACION where PLANEACION.STBG NR like '*" & strPalabrasegun da & "*'"
Set taBla = prueBa.OpenReco rdset(strSQL, dbOpenDynaset)
With taBla
.MoveLast
.MoveFirst 'Voy al primer y al último registros con el objetivo de contar los registros.
cuentapalabra = taBla.RecordCou nt
Texto26 = cuentapalabra
That does it fast, no problem if it keeps so.
But there's another problem, the fields "set" and "part number" could be the same in several entries (does it mean "registro") . I mean, the same combination part number-set could be in many entries. So, it could be fixed with a select distinct, but the fields stock number or stock make it change. I know it's the same but I can't find a way to show it once.
Now I'm trying to use a sum, but I'm having problems again:
SELECT Sum(PLANEACION. LSLGBE) AS SumaDeLSLGBE
FROM PLANEACION
GROUP BY PLANEACION.STBG NR;
This makes it, but the same problem: how do I get it in an only query or how do I use it for VB. Well, actually I have it in VB, but I don't know the way o show the result from the sql statement in a textbox.
As you can see I'm not fine and maybe I'm not in the right way, so if you can help me to get the three queries in one or to get the resulta from my query to a textbox.
Thanks for the time and I hope you have understood -because the trouble is a little confussing and my English is not good enouh-, if you didn't, please write to this e-mail:
**Email removed as per site rules**
Thank you again and have a nice -amazing- day!
Comment