Calculation using field in linked table

[gbrigman]

I make belts. Belts are made in bundles of 20. An order is placed for 400 belts. I need to generate bundle tickets(autonumber) for each bundle. The bundle size information comes from linked table 2. The Job number comes from linked table 1. How do I create a query that will autonumber from the query until it reaches 400 and stop.

Thanks,

Guy

 

[tlbroadbent]

I'm not sure what you mean, exactly. You can use TOP N in a query to limit the number of records selected.

Select TOP 400
From table1
Join table2
on table1.keycol=table2.keycol
Where <criteria>

If you need something else, please provide table schemas and data examples from each table. If you already have created a query that selects the rows, please post it also.

Thanks,
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[gbrigman]

First, let me say sorry for being such a newbie.

SQL connection to dbo_jomast, dbo_joitem, and dbo_inmast

SELECT DISTINCTROW dbo_jomast.fjobno, dbo_joitem.fpartno, dbo_joitem.fmqty, dbo_inmast.fnusrcur1
FROM (dbo_inmast INNER JOIN dbo_jomast ON (dbo_inmast.frev = dbo_jomast.fpartrev) AND (dbo_inmast.fpartno = dbo_jomast.fpartno)) INNER JOIN dbo_joitem ON dbo_jomast.fjobno = dbo_joitem.fjobno
WHERE (((dbo_jomast.fjobno)=[Job #]));

This appends 1 item into a 4th table Joext(access table).
Lets say the job i7311-0000(dbo_jomast.fbobno)is for 400 (dbo_joitem.fmqty)belts. Each belt bundle is 20 (dbo_inmast.fnusrcur1). I need this query to generate rows in table joext for each bundle until all bundles are accounted for (20 bundles@20 each=400). Table joext consists of fcjobno (input from query) fcbundleno (autonumber) and fcbndlqty (input from calculation in query).

I hope this makes sense.

Guy

 

[gbrigman]

Is it impossible to do this calculation the way that I need to?