2
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.LSLANR, RHDBD_16_TEILS.TEMAGR, RHDBD_16_LGBS.LSLGBE INTO PLANEACION
FROM RHDBD_16_LGBS, RHDBD_16_STRUS, RHDBD_16_TEILS
WHERE (((RHDBD_16_STRUS.STFIRM)="5") And ((RHDBD_16_STRUS.STKOMP)=(RHDBD_16_TEILS.TETENR) And (RHDBD_16_STRUS.STKOMP)=(RHDBD_16_LGBS.LSTENR)) And ((RHDBD_16_STRUS.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_TEILS.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:
strPalabrasegunda = STBGNR
Set prueBa = DBEngine.OpenDatabase("C:\Documents and Settings\mzarate\
Mis documentos\PRUEBA\PLANEACION_DE_LA_PRODUCCION_PEM. mdb")
strSQL = "SELECT * FROM PLANEACION where PLANEACION.STBGNR like '*" & strPalabrasegunda & "*'"
Set taBla = prueBa.OpenRecordset(strSQL, dbOpenDynaset)
With taBla
.MoveLast
.MoveFirst 'Voy al primer y al último registros con el objetivo de contar los registros.
cuentapalabra = taBla.RecordCount
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.STBGNR;
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!
