Referenced ID twice in same Table?

[Romper]

Hi all,
Can you use the same referenced ID more than once in the same table? I have a database for my stamps. The exact same type of stamp may be kept in more than one location (Album). So, I have TBL_Albums which has two fields "AlbumID" and "AlbumName". Can I use "AlbumID" in my main RecordSource "TBL_Main" more than once to reflect the different Albums this stamp may be found in without causing any major problems to the database?

If this is okay, I could then assign different alias names to each "AlbumID" column in "TBL_Main" such as Location1, Location2 etc to be able to tell them apart on forms etc.

Is this good practice, or is there a smarter option?

 

[boblarson]

That is just a normal part of a normalized database. You have

Albums Table
AlbumID
AlbumName

And then AlbumID is a FOREIGN KEY in the other table and that can be used as many times as necesary provided it isn't just a complete duplicate record. Then it would be redundant.

 

[Romper]

Thanks Bob,

Just wanted to make sure I was on the right path.

 

[Romper]

Okay, there's a slight issue!

When I try to create a second "AlbumsID" column in TBL_Main I get an error message:-

"You already have a field named 'AlbumsID.'

What am I doing wrong?

 

[boblarson]

You don't put a second COLUMN in your table. You use AlbumID to store RECORDS.

What fields do you have in TBL_MAIN?

 

[Romper]

TBL_Main has very many fields, most of which are ID fields for foreign keys such as "CountryID", "CatalogueID", "WatermarkID" and such.

So, I only put "AlbumsID" once in TBL_Main, but use it as a combo box as many times as required on FRM_Main and apply the alias there, is that correct?

 

[boblarson]

You may need to adjust your table structure a bit. I don't have time right now to go into it.

 

[Romper]

Oh, okay,

Hopefully someone else might know what I need to do?
Thanks anyway.

 

[John Big Booty]

Can you post a copy of what you've done so far?

 

[Romper]

Hi John,

It's probably too big, it's at approx 14mb now and still in design phase. Would it help if I just uploaded TBL_Main and TBL_StampAlbums? Every time I try to move forward in full flight I hit another snag!

Do you understand what I've said about the need to reflect that a certain type of stamp can be held in more than one location (Album)? And therefore I need to use AlbumID more than once in a table or form.

Let me know what you'd like to look at and I'll convert it back to 2002 and upload.

Thanks John.

 

[Romper]

I've attached a refined db copy whch contains TBL_Main, TBL_StampAlbums and a few others.

You will see approx 3 quarters of the way across TBL_Main i have an "AlbumID" column followed by columns CWUSLoc1QTY, CWUSLoc2QTY and so on. Each one of the 'CWUSLocQty' columns will represent a numeric value (quantity) of the same type of stamp. So I was thinking I needed to have "AlbumID" in front of each of these (as i've done with the first) so that the table would then reflect as a record:-

"AlbumID" - "CWUSLoc1Qty" - "AlbumID" - "CWUSLoc2Qty"
.....1................ 1 ....................3................ 10
...BLK1.............. 1................. BLK3.............. 10

To reveal that in Album named BLK1 (ID=1), i have 1 of these stamps and in album named BLK3 (ID=3), i have 10 duplicates and so forth.

I hope I've explained that well enough.

So now you see my need for using the "AlbumsID" more than once.

Thanks :banghead:

 

[John Big Booty]

Have a look at this data model. I suspect you could probably use it as a starting point.

The model comes from here, perhaps there is something even closer that you could use.

 

[Romper]

Thanks John,

I had a look at quite a few different models and couldn't quite find one that had the same sort of scenario. I'm thinking that on this occasion it might be easier just to either duplicate TBL_StampAlbums so that I can use one for each of the six possibly locations, or, just place the same look up six times on the one form for each location and not have it as an ID field in the TBL_Main RecordSource. I know this goes against the grain, but I'm going backwards faster than I am forwards and I don't really anticipate running many album reports or queries.

Thanks for the suggestion though.

 

[Romper]

FYI,

What I've done is created fields "Loc1“, "Loc2“, "Loc3“ etc in front of each of their respective qty fields in TBL_Main. Then I shall just convert each of these fields on the form to a combo box that looks at TBL_StampAlbums. Just done a test run and it works fine. For the sake of my sanity, it's probably the best solution at this stage.