My company has several locations, each with a number 10 through 24. i have a table (tbl1) which shows the inventory at each location, for each stock number in an order, and i've created a crosstab query that shows the value for that stock number at each location side-by-side (the table is a bit awkward). The problem is that if there is no inventory at one of the locations for any of the stock numbers queried, that location is not listed as a column heading.
A future step needs to determine if there is enough inventory at a *specific* location (18) to fill an order. It compares to the crosstab for this. However, if there is no inventory at 18 for any of the stock numbers in the order, there is no crosstab column to compare it to, and it errors out.
i have tried creating a generic table with all locations and the values set to zero, with the idea of creating an update table to update those locations with the inventory from the crosstab, but this causes the same error.
Any other suggestions? My next step is to call a module that cycles through an array of locations, but i fear this could cause the query to run very, very slowly.
A future step needs to determine if there is enough inventory at a *specific* location (18) to fill an order. It compares to the crosstab for this. However, if there is no inventory at 18 for any of the stock numbers in the order, there is no crosstab column to compare it to, and it errors out.
i have tried creating a generic table with all locations and the values set to zero, with the idea of creating an update table to update those locations with the inventory from the crosstab, but this causes the same error.
Any other suggestions? My next step is to call a module that cycles through an array of locations, but i fear this could cause the query to run very, very slowly.
