Background:
I have been working on a sport card database for a long time in MS Access but am at a point where I needed to enlist help from a former colleague who specializes in Oracle, SAP, PostgresSQL.
Summary:
In MS Access I had so many M2M relationships that were handled through I believe what are called bridge or junction tables. My colleague has me re-designing the database in excel just to map the tables initially...
cmo187265
Last Activity: Feb 9 '22, 05:39 AM
Joined: Aug 26 '18
Location:
-
Lookup fields still evil?
-
Lookup fields, still evil outside of Access?
Intro:
I have been working on a Sports Card database and have many questions posted on this forum related to creating the database through MS Access. After some time (2 yrs) I have decided to enlist the help of an old work colleague who had experience with databases (SAP, Oracle, Postgres) because I could not understand how to write the queries over all these many to many relations in Access. I've just not made significant progress on... -
No problem NeoPa. I can assure you that I continue to chase rabbits that are at least initially relevant to my questions while waiting for replies so I'm no just waiting idly.
Makes sense about external links so that the body of knowledge can grow within the forum.
Look forward to any feedback on my posts #5 or #6 when you have time.
Thanks as always. -
I believe I found a solution by reading through another person's similar issue on another forum.
It is likely against etiquette to post a link to another forum but the solution is to clear out what Access autopopulates as 0 in the default value property in the field of the TBL_Sales that is located as a FK in the TBL_InventoryTr ansactions.Leave a comment:
-
Thank you Neopa. Sorry I was not clear perhaps.
One order can contain many inventory items, one sale can also contains many inventory items.
Concerning 1 unique inventory item:
Can it have multiple related sales? - NO
Can it have no related sales? - YES
Can it have multiple related orders? - NO
Can it have no related orders? - NO
So when I enter an order transaction I will...Leave a comment:
-
As always Neopa, thanks for your help.
Are you suggesting to create 2 MTM Juntions, one between TBL_InventoryTr ansactions & TBL_Sales as well as one between TBL_InventoryTr ansactions & TBL_Orders?
I don't have Inventory as having a MTM with either Order or Sales because each item in inventory is a unique item. A card is appraised/graded, encased by a grader, and then stamped with a unique barcode that doesn't...Leave a comment:
-
This helped me.
"Conceptionally , you know in your mind that Washington, GA is not the same as Washington, IL so they should be defined that way digitally."
Thank you all!Leave a comment:
-
When inventory is not yet sold
Intro:
I am still (painfully) trying to create a sportscard database. I have other questions related to this endeavor in other threads in this forum.
Background:
I need to database to be able to store Order data (items I buy) and Sales data (items I sell).
One Order can later be part of many Sales (i.e. many items can be bought in one Order transaction and be split up later into multiple Sale... -
Many many to many headscratchers
Background:
I am building a sports card database (or trying to). (In MS Access)
Situation:
The sports card database necessarily will require a lot of many to many table relationships.
Problem:
Me. I am have trouble grasping some instances of the many to many concept.
Example1: Student to Classes (I understand this)
One student can have many classes
One class can have... -
Thank you NeoPa. Also, thanks kindly for your support when I have asked questions in the past.
My next Q for another thread may be how I can incorporate current franchises. For example many of these historic teams belong to one franchise history and I need to be able to search for both current franchise or specific historic teams.
Thanks again!Leave a comment:
-
I could not find how to attach an image until now. Thanks again for all your help!...Leave a comment:
-
Too many, many to many?
Background:
I have slowly and painfully been working on creating an Access database for sports cards.
Database Needs:
For the purpose of this post, a user (me) will need to be able to search for cards by Sport, Location, or Team Name (or a combination thereof).
Problem/confusion:
Could someone kindly look at my screenshot to see if I am overthinking my table relationships? Everything is so connected... -
Can I plan for a split Access database?
Introduction:
I am creating a sport card database to record inventory and transactions. I have a few good posts on this forum if you are able to search by my username if it helps understand better.
Issue:
I anticipate, of the many normalized tables, 3 tables will continue to grow through the life of the database I and understand that Access has a resource limit of 2GB. I anticipate for example a table TBL_Inventory... -
Thank you Neopa.
After your post, I went back and noticed only a field set to date/time data type had a lookup tab that wasn't populated.
I will assume going forward after your and Cactusdata's help that as long as my field display controls are set to TextBox they are not lookup fields.
As always, thanks for your patience and help. Good to hear from you.Leave a comment:
-
Yea...I can choose from Combo, List, or Tex Box but it's all under the Lookup tab so is it still OK?
Doesn't the fact that the Lookup tab is populated with info now under Display Control, even if it is as a Text Box, make this a lookup field?Leave a comment:
-
Thank you Cactus. That is what I did. Are you able to see the images I have been attaching?
What i refer to as relationship view, you refer to as relationship window, so I apologize if I used the wrong term there.
The central point of my confusion remains then. When I do exactly as you have instructed in your last post and as I have been doing all along anyway (creating relationships with the relationship window),...Leave a comment:
-
Thanks again Cactus. Maybe my question isn't clear. I understand conceptually that if I want to display some data I will need to query that data.
My question is about linking tables (relationships) . So I will re-create the steps.
1. In relationship view I show 2 tables, say tblCustomers and tbl Transactions.
2. In relationship view I drag the PK of tblCustomers to a FK in tblTransactions to create a relationship....Leave a comment:
-
Thank you cactusdata. So how to I join related tables if I cannot lookup other tables? For example my screen shot showed two tables joined by the CustomerID existing in both tables and joining from a lookup.
I did break Customer out into it's own table for the sake of normalization.. ..
Thanks again for your help.Leave a comment:
-
Access: Will lookups in tables cause issues?
As a point of reference I have a few posts on this site for a baseball card database I am trying to create (very slowly).
Reference:
https://bytes.com/topic/access/answe...-normalization
Summary:
In the linked post above I have a Join Table I have created so that I am able to further normalize and "link" all of the tables to accommodate... -
I have created two table design plans in attempt to solve my dilemma as stated above. The 1st didn't seem to work for me or I couldn't figure it out.
Attempt 1:
CardTBL
CardID-autonumber (PK)
TeamTBL
TeamID-auto (PK)
PlayerTBL
PlayerID-auto (PK)
CardPlayerTBL
CardPlayerID-auto (PK)
CardID (FK)
Player (FK)
CardTeamTBL
...Leave a comment:
No activity results to display
Leave a comment: