SQL Query to add RowNum and ColNum in PartsLocation Table

[MadTom]

I created a Table PartsLocation with LocationID, RowNum, and ColNum. I have parts bins with 5 bins across and 10 rows down. I would like to add Numbers into fields to correspond with parts Location, as RowNum 1, ColNum 1> RowNum 10, ColNum 5. I will use LocationID to show the Location of the part. Maybe later add another field to show Location inside Bin. I just started using Access and need to be pointed in the right direction, I can Create Table and basic commands in SQL, been thru a few books and many youtube videos. Thanks!

[davegri]

I have read you post several times and don't understand how your table is set up.
If you have 50 records in tblPartsLocation for each bin, you don't need RowNum, ColNum identifier fields in the record because each pigeon hole is a location corresponding to one of the 50 records. A compound key could identify each pigeon hole uniquely.
If you have one record in tblPartsLocation for each bin, and 50 fields for the pigeon holes, you have a spreadsheet and need to start over. Access is not Excel.

[MadTom]

I could number each bin 1-50, I have more than one so I could name them T1-50, B1-50, C1-50. In my Parts Table I have PartsLocation field. Some bins have 4 locations in each bin, so I would list it as B1.1 or C2.4. I was looking for a way to add row & column numbers into fields with a Query rather then adding each by hand. I could use LocationID to locate the bin. Thanks

I have read you post several times and don't understand how your table is set up.
If you have 50 records in tblPartsLocation for each bin, you don't need RowNum, ColNum identifier fields in the record because each pigeon hole is a location corresponding to one of the 50 records. A compound key could identify each pigeon hole uniquely.
If you have one record in tblPartsLocation for each bin, and 50 fields for the pigeon holes, you have a spreadsheet and need to start over. Access is not Excel.

[davegri]

Some bins have 4 locations in each bin, so I would list it as B1.1 or C2.4.

So 50 records per bin won't work. There could be more than 50 locations per bin.
You will need a consistent and uniform way to specify Bin, Rows, Locations and Columns to uniquely identify every possible location. After you've worked that out, run that by us and we can devise a looping code algorithm to create your tblPartsLocation table.
In this table there could be one yes/no field to indicate if the location is available.

[Micron]

I have parts bins with 5 bins across and 10 rows down

I take it this has nothing to do with your table - it's the bin layout. Then what about

tblBins				tblComp
BinIDPK	RowNO	ColNO		BinIDFK	CompNO
1	1	1		1	1
2	1	2		1	2
3	1	3		1	3
4	1	4		1	4
5	1	5		5	1
6	2	1		5	2
7	2	2		5	3
8	2	3		8	1
9	2	4		8	2
10	2	5
11	3	1
12	3	2
13	3	3
14	3	4
…	…	...
46	10	1
47	10	2
48	10	3
49	10	4
50	10	5

Note that bins 2,3,4 have no compartments. 1 has 4; 5 has 3; 8 has 2. Bin 11 is in row 3, column 1. If you modify a bin to add/delete compartments, update the compartment table.

[MadTom]

Yes! This is what I would like to do. I would like a SQL Query to add this info. to the fields. Then I would add BinIDPK to the PartsLocation to tell me where the part is located. Thanks for the reply!

[Micron]

I see I wasn't clear on one thing. The bin table main id field should be an autonumber field, and those values would be the bin id fk's in the compartment table, in which case you'd have

BinIDPK	BinNO	RowNO	ColNO
autonumber (e.g. 25)	001	1	1

BinIDFK	CompNO
25	compartment letter or number (e.g. "A")

This provides the ability to alter bin names (BinNO field) without having to modify the compartment table values for any bin number. If you change BIN 01 to BIN 001 for example, only the BinNO has to change. Once you have the tables created, create a select query, joining the tables from BinIDPK to BinIDFK. Test there is no problem with the query by opening it and trying to add data for all fields (except for the autonumber field, which is automatic). If OK, create a form based on the query and you're good to go.

[orange]

MadTom,

When I read your post, I'm confused as was davegri. I don't mean to be negative and dissuade you, but it seems that you have not clearly identified and worked through your requirement. It is more difficult than many think to write a clear description of what you are trying to accomplish in plain English. People, especially those new to database, think that Access is going to solve the problem and quickly jump into a physical database. They then start a trial and error approach to understand Access, some commands and the required syntax. Of course that will likely work in the due course of time. However, it is a long and frustrating journey.
Here is a link to information on Database Concepts, Planning and Design that will help. You will save yourself some time and frustration by working through the tutorials from RogersAccessLibrary mentioned in the linked info. Spend 30-45 minutes working through a tutorial and experience the design process. The other videos and articles will help with concepts and techniques that can be used with any database.

Micron has given you an approach that can be used as a guide, but I still recommend you look at the linked info, make a clear description, do a tutorial or 2, then apply what you learned to your own situation.

Here is a draft data model provided to a poster who had many warehouses in different locations. Each warehouse had many shelves, and each shelf had many bins. Each Bin contained material.



Not the situation you have, but similar in concept. It may give you some insight to your project.
Good luck.

[MadTom]

I like all the input to what I want to do with Access, It's not very computated, I have a Parts Table with Resistors, Diodes, IC's etc., would like to locate them with my database. I have bin cabinets (not all the same), so I would like to add a Table Joined to my Parts, so 1K resistor could be in Location BinID 6, I would know it's in Row 2, Col 2, Comp 2. Now I can add all the numbers in my tblBins one at a time, but is there a way to Run a SQL Query to add all the numbers for 10 Rows 5 Columns 4 Compartments. I could alter this Query for different Bins for different parts, as tblResBin, tblCapBin, tblIcBin, etc. So I'm looking for some SQL code or point me in some direction so I can learn more about Queries and SQL, not into VBL yet. Thank you all for your comments!

Example below:

tblBins				tblPart
BinIDPK	RowNO	ColNO	BinCompartment	BinIDFK	CompNO
1	1	1	1	1	1
2	1	1	2	1	2
3	1	1	3	1	3
4	1	1	4	1	4
5	1	2	1	5	1
6	2	2	2	5	2
7	2	2	3	5	3
8	2	2	4	8	1
9	2	3	1	8	2

I take it this has nothing to do with your table - it's the bin layout. Then what about

tblBins				tblComp
BinIDPK	RowNO	ColNO		BinIDFK	CompNO
1	1	1		1	1
2	1	2		1	2
3	1	3		1	3
4	1	4		1	4
5	1	5		5	1
6	2	1		5	2
7	2	2		5	3
8	2	3		8	1
9	2	4		8	2
10	2	5
11	3	1
12	3	2
13	3	3
14	3	4
…	…	...
46	10	1
47	10	2
48	10	3
49	10	4
50	10	5

Note that bins 2,3,4 have no compartments. 1 has 4; 5 has 3; 8 has 2. Bin 11 is in row 3, column 1. If you modify a bin to add/delete compartments, update the compartment table.

[orange]

Before you get involved in queries and SQL, I recommend you get your tables and relationships designed and tested. It seems you have Excel experience which may not be very helpful when working with database/Access.
Good luck.

[MadTom]

I have my Tables CapacitorsTbl, RestistorsTbl, DiodeTbl, TranistorsTbl, HardwareTbl, IcTbl. I have them Joined to a LocationTbl and started to name each location (bin), but was looking for an easier way. Watched many youtube videos and half a dozen books from the library. I know a bit about Excel and can do some programing in C++ and Arduino IDE, years ago I used and programed in Paradox, Now using Access in office 365, just a hobby for a retiree. Thanks!

[davegri]

I like all the input to what I want to do with Access, It's not very computated, I have a Parts Table with Resistors, Diodes, IC's etc., would like to locate them with my database. I have bin cabinets (not all the same), so I would like to add a Table Joined to my Parts, so 1K resistor could be in Location BinID 6, I would know it's in Row 2, Col 2, Comp 2. Now I can add all the numbers in my tblBins one at a time, but is there a way to Run a SQL Query to add all the numbers for 10 Rows 5 Columns 4 Compartments. I could alter this Query for different Bins for different parts, as tblResBin, tblCapBin, tblIcBin, etc. So I'm looking for some SQL code or point me in some direction so I can learn more about Queries and SQL, not into VBL yet. Thank you all for your comments!

Example below:

tblBins				tblPart
BinIDPK	RowNO	ColNO	BinCompartment	BinIDFK	CompNO
1	1	1	1	1	1
2	1	1	2	1	2
3	1	1	3	1	3
4	1	1	4	1	4
5	1	2	1	5	1
6	2	2	2	5	2
7	2	2	3	5	3
8	2	2	4	8	1
9	2	3	1	8	2

This example is flawed. How does it differentiate one bin from another?
It needs another column between BinIDPK and RowNo named perhaps BinNo.

[MadTom]

Yes I should add BinNum, no problem! Maybe I can't do what I want in a Query? I just don't want to add all the numbers one at a time, but I could, so I want to learn SQL. Thanks!

[orange]

Madtom,

There are a lot of us here who are retirees.

Maybe I can't do what I want in a Query

yes you can provided that your tables and relationships are designed to meet your requirements.
Database always starts with a requirement (outputs, business rules...) that you can model with pencil and paper. Some test data and scenarios can be used to vet the model. When your paper based model satisfies the requirement, you can use it as ablueprint for building the database.
The link I provided in post #8 has lots of useful info re Database planning and Design and more. There are many free videos on youtube for using Access and SQL.

[MadTom]

Thanks Orange, I did go over the many links and videos, some are great. I'm not looking to plan a new database, I'm looking for a SQL Query that could added incremental numbers into more than one field, but will have to loop thru field 1,2,3. Field1 is 1 to 10, Field2 is 1 to 5, Field3 is 1 to 4 then END.

Is there anyone out there that can do this?

Tablexxx
TableID Field1 Field2 Field3
1 1 1 1
2 1 1 2
3 1 1 3
4 1 1 4
5 1 2 1
6 1 2 2
7 1 2 3
8 1 2 4
9 1 3 1 ...

100 2 1 1
101 2 1 2 ...
xxx 10 5 4 End