Showing number series in query for grouped items

[Flanders]

Hi,

I have a stock table which has serial numbers for each item. I want a query
which groups the item types and shows the first and last serial numbers for
that item type along with how many. Eg. 10 x mobile phones first serial
number = xxx1, last serial number = xx10.

I tried the first, last, min and max functions but they continue to group
and show each of the 10 items individually.

Any ideas??

Thanks

 

[Michael Gramelspacher]

Hi,

I have a stock table which has serial numbers for each item. I want a query
which groups the item types and shows the first and last serial numbers for
that item type along with how many. Eg. 10 x mobile phones first serial
number = xxx1, last serial number = xx10.

I tried the first, last, min and max functions but they continue to group
and show each of the 10 items individually.

Any ideas??

Thanks

SELECT Stock.Type_Code,
COUNT(Stock.Serial_Number) AS [Item Count],
(SELECT TOP 1 A.Serial_Number
FROM Stock AS A
WHERE A.Type_Code = Stock.Type_Code) AS [First Serial Number],
(SELECT TOP 1 A.Serial_Number
FROM Stock AS A
WHERE A.Type_Code = Stock.Type_Code
ORDER BY A.Serial_Number DESC) AS [Last Serial Number]
FROM Stock
GROUP BY Stock.Type_Code;

 

[Flanders]

Thats great thank you but I need to append the results to an "Invoice" table
which already exists. Is there no way this can be done in an append query?

Hi,

I have a stock table which has serial numbers for each item. I want a query
which groups the item types and shows the first and last serial numbers for
that item type along with how many. Eg. 10 x mobile phones first serial
number = xxx1, last serial number = xx10.

I tried the first, last, min and max functions but they continue to group
and show each of the 10 items individually.

Any ideas??

Thanks

SELECT Stock.Type_Code,
COUNT(Stock.Serial_Number) AS [Item Count],
(SELECT TOP 1 A.Serial_Number
FROM Stock AS A
WHERE A.Type_Code = Stock.Type_Code) AS [First Serial Number],
(SELECT TOP 1 A.Serial_Number
FROM Stock AS A
WHERE A.Type_Code = Stock.Type_Code
ORDER BY A.Serial_Number DESC) AS [Last Serial Number]
FROM Stock
GROUP BY Stock.Type_Code;

 

[Michael Gramelspacher]

Thats great thank you but I need to append the results to an "Invoice" table
which already exists. Is there no way this can be done in an append query?

You want to store three calculated values in a table? That is not good
practise. It makes no sense to me. Anyway, you have not provided any information
about the Invoice table.

 

[Flanders]

Hi,

I know this is not an ideal scenario. It is being used as a temporary fix
for a sales department who allocate stock to a dealer and then produce an
invoice based on that. The stock is allocated but as I do not want lines and
lines on the invoice for the same items I wanted to group them together to
show how many of each item had been allocated. I have the majority of this
done but have now been asked to include the serial numbers range on the
invoice for the items listed.

If this cannot be done they will have to just make do !!

The invoice table is just a record of the invoices produced so it has
invoice number, number of items, item type, first serial number, last serial
number, cost per item and total cost. Most of which are calculated fields
based on the stock allocation table. I just cannot get it to show first and
last serial numbers !

Thanks

 

[Flanders]

I have now fixed the problem ! Thank you

Hi,

I know this is not an ideal scenario. It is being used as a temporary fix
for a sales department who allocate stock to a dealer and then produce an
invoice based on that. The stock is allocated but as I do not want lines and
lines on the invoice for the same items I wanted to group them together to
show how many of each item had been allocated. I have the majority of this
done but have now been asked to include the serial numbers range on the
invoice for the items listed.

If this cannot be done they will have to just make do !!

The invoice table is just a record of the invoices produced so it has
invoice number, number of items, item type, first serial number, last serial
number, cost per item and total cost. Most of which are calculated fields
based on the stock allocation table. I just cannot get it to show first and
last serial numbers !

Thanks