UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Make Table With Custom Index, Access 2010    
 
   
jukos
post Sep 12 2018, 11:02 AM
Post#1



Posts: 19
Joined: 31-May 16



I am trying to generate a table that takes an existing table finds all common rows in two fields and enumerates them. So in the original table I have two fields, shelf and bin. I would like to find all records where these two field match any other records. ie find all records in which shelf =a and bin =1,and assign it a number starting at 1. This number would be the locationid field of the new table. See image for clarity. This solution doesn't have to be elegant or reusable...I just want to run a query which will generate the table once.


thanks, John
This post has been edited by jukos: Sep 12 2018, 11:04 AM
Attached File(s)
Attached File  adding_index.gif ( 53.97K )Number of downloads: 1
 
Go to the top of the page
 
MadPiet
post Sep 12 2018, 05:26 PM
Post#2



Posts: 2,494
Joined: 27-February 09



you could create a query like

SELECT DISTINCT ShelfID, BinID
FROM <mytable>
ORDER BY ShelfID, BinID

and then append that to a table

CREATE TABLE DestinationTable (ShelfBinID INT IDENTITY, ShelfID INT, BinID INT)

Where ShelfBinID is an autonumber (aka identity...) and the combination of ShelfID and BinID are unique
Go to the top of the page
 
jukos
post Sep 14 2018, 01:18 PM
Post#3



Posts: 19
Joined: 31-May 16



Thanks for reply MadPiet. I was able to create a query with the first part of your code"select distinct....." by pasting into the SQL window of qbe grid. However I am not sure how to implement the rest of your code. When I paste that into the SQL window I get an error "syntax error in CREATE TABLE statement.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 05:39 AM