Inventory Db - printing a report that matches real layout

[Rich99]

Hi,

I'm a reasonably competent Excel user, but complete novice at access, so if I'm going about this completely wrong, please correct me. I want to track inventory. All our items are tubes that are stored in boxes in a 10x10 grid. So I have a single access table with columns for tube ID, box ID, and position in the box (numbered 1-100). That works fine in terms of 'where can I find tube x'. However it's also useful to be able to look at the physical box & check that there are tubes where should be tubes, and empty spaces where there should be empty spaces. So for each box in the database, I want to print out a 10x10 grid, with the appropriate tube ID's in the appropriate place. Then I can easily check the layout in the database against the layout in the actual box. Is this possible? If so, how would I go about doing it?

Thanks!

 

[plog]

Yes, its wrong. Database tables should grow vertically (with records) and not horizontally (with columns). When you start numerating fields (Position1, Position2...Position100), you need to have a new table.

To keep track of tube position, you would need a table with just 3 fields:

TubeID, BoxID, Position


That's it. Then for your desired results, you would most likely use a cross-tab query (https://support.office.com/en-au/ar...ab-query-8465b89c-2ff2-4cc8-ba60-2cd8484667e8)

 

[plog]

Thinking this over, your desired results would be better achieved if you used cartesian coordinates (X and Y axises). The first position would be 0,0 and the last would be 9,9. It might be easier to think of this as Row and Column positions instead of X and Y. That means the table I described earlier would have this structure:

TubeID, BoxID, BoxColumn, BoxRow

That would lend itself better to a report that looks like the one you described.

 

[Rich99]

Thinking this over, your desired results would be better achieved if you used cartesian coordinates (X and Y axises). The first position would be 0,0 and the last would be 9,9. It might be easier to think of this as Row and Column positions instead of X and Y. That means the table I described earlier would have this structure:

TubeID, BoxID, BoxColumn, BoxRow

That would lend itself better to a report that looks like the one you described.

I can see how this would work, unfortunately the boxes are physically stamped with the numbers 1-100, so we really need to use the numbers to ID the positions.

Would the cross-tab query work with just numbers? Also, in answer to your other reply - I do have the columns just as you described, and am adding new records as rows.

 

[plog]

Would the cross-tab query work with just numbers?

I don't understand the question.

Also, your table where you assign positions is in the format I described? It only has 3 fields and not 102?

 

[Rich99]

I don't understand the question.

Also, your table where you assign positions is in the format I described? It only has 3 fields and not 102?

My table just 4 columns - ID, tube name, box name, box position. Box position is a number between 1-100. For each Box name, I want to output a 10x10 grid, such that the tube names at positions 1-10 are in row 1, tube names for positions 11-20 are in row 2, 21-30 are in row 3 etc etc.

I don't understand how that can be done with a cross-tab query?

 

[plog]

First you need a sub-query. This query will get the data from your structure into the Row, Column one I detailed. For example, Position=15 is equivalent to Row=2, Column=4. That's because Position=1 is equivalent to Row=0 and Column=0.

So, use this query to prepare your data for the cross-tab:

SELECT YourTableNameHere.[table name], YourTableNameHere.[box name], Int(([box position]-1)/10) AS BoxRow, ([box position]-1) Mod 10 AS BoxColumn
FROM YourTableNameHere;

Save that with the name like BoxPosition_sub1. Then you build your cross-tab query using it.

 

[Rich99]

OK, I think I'm getting there. I successfully ran your query, and got an output that looked correct, with columns for boxrow & boxcolumn. Then I ran the crosstab wizard on the query. It wanted to do a calculation at the intersections - whereas I just want the text. I chose 'first' as the function, and it seemed to output something like what I was expecting. However I only got 1 table - whereas I have 2 boxes in the database. The output table seemed to be some sort of mixup of the data from the 2 boxes?

 

[plog]

You should apply criteria to the sub query or the cross-tab to filter it to just the table you want to see.

 

[Rich99]

You should apply criteria to the sub query or the cross-tab to filter it to just the table you want to see.

OK, thanks - there isn't a way to make it iterate through the different boxes & make a table for each?

 

[plog]

Your use of 'table' is confusing. It means something very specific when you are talking about a database: https://en.wikipedia.org/wiki/Table_(database)

You can make that cross-tab query display all your boxes and locations if you want. You should bring in the BoxID, make sure it says Group By underneath it, make it a row heading and then sort on that field in Ascending order.

 

[Rich99]

Apologies, I can see how that's confusing. I'll have a play and get back to you if I need more help!

 

[stopher]

You can print out the 100 records in columns/rows in Page Setup - columns tab and set the number of columns to 10.

You will need to make the report quite small (the size of 1 box) so that it can fit 10 across.

see example

 

[Rich99]

You can print out the 100 records in columns/rows in Page Setup - columns tab and set the number of columns to 10.

You will need to make the report quite small (the size of 1 box) so that it can fit 10 across.

see example

I hadn't considered that as an option, so thanks - however I think the previous solution is more flexible & less of a 'kludge'!!

 

[Rich99]

You can make that cross-tab query display all your boxes and locations if you want. You should bring in the BoxID, make sure it says Group By underneath it, make it a row heading and then sort on that field in Ascending order.

Works perfectly thanks!