lonerjohn
Last Activity: Feb 12 '19, 02:54 PM
Joined: Jan 29 '19
Location:
-
I edited my tables. I have a primary key for each table that's an autonumber. The foreign keys is what I can't seem to wrap my head around. I have no relationships setup currently. I've created a Word document to show the current table setup along with a brief explanation as to what it's for. I did use short text as default on all the fields. I can change that. The database won't contain more than 500 records and users will be selecting records from... -
And, will the foreign key in one table always be the primary key in another table? Cause if that's the case, that makes it easier to comprehend.Leave a comment:
-
"In our TblContracts, we store 1234 which points to the correct record in the Payee Table. It is picked from a Combo Box.
This is a Foreign Key which points to a record in another (Foreign) Table"
This is referring to the combo box that would show both the AutoNum and the PayeeName so the user picks the right one, but it stores the AutoNum, right?
I do typically split the front end and back end into...Leave a comment:
-
I'm definitely willing to put in the time. Unfortunately, I had some damage to my house from a recent storm and this weekend I finally was able to get the insurance company in to take a look at it so the repairs could start to be made. It's been a rough month.
I guess the part that I'm trying to understand is the generic setup. For example: I understand tblContracts should be its own table because I want to track all of the information...Leave a comment:
-
I'll try and go over all of this information this weekend and see what I can come up with. I really appreciate the assistance from both of you.Leave a comment:
-
SFSVendorID, FederalID and CharitiesReg are fields associated with the Payee. They're unique numbers that link with various other state and federal agencies.Leave a comment:
-
In post #9, are you suggesting a table for each type of contract? Attorney would be a type of contract and wouldn't have an AttorneyID.
If the FY value isn't being stored in fields, how would I differentiate between the value of contract FY1415, for example, being $150,000 and the value of FY1516 being $183,000 and FY1617 being $192,000? The contracts are typically 5 year contracts with varying funds each year.
...Leave a comment:
-
Given the suggestion to use a long number as the primary key, I think ContractNum would be the best bet. ContractNumbers will be unique, will start with the letter "C" and will be followed by either a 6 digit number (i.e. C123456) or a 5 digit number followed by the letters "GG" (i.e. C12345GG).Leave a comment:
-
Here are the fields from tblMain
[imgnothumb]https://bytes.com/attachments/attachment/9868d1549571311/tblmainfields.j pg[/imgnothumb]...Leave a comment:
-
It's to keep track of the contracting information that we need to report on. For example, we will likely have to report all contracts of a specific type (Attorney) for a specific range (only values from FY1415-FY1819) that serves a specific county (New York). I would then need to know the total dollar value of each payee, as well as a total value of all the contracts that fit into the range specified. Everything will be Text with the exception...Leave a comment:
-
Help with many to many relationship setup
My office has an Excel spreadsheet that contains information they want to turn into a database. Some of the Excel cells contain numerous pieces of information that I'd need to store individually in Access. So, I've been advised to do many-to-many relationships. My tables are:
tblMain: contains all of the fields across all of the tables. Primary Key is RecNum (autonumber field)
tblContracts: contains... -
Believe me, I'm right there with you. I'm actually contemplating working on this from home to start it from scratch, even though I'd have to do it all in my personal time. I was really hoping to avoid that.Leave a comment:
-
The sheets are updated anytime a contract's details have been updated. So a record would be updated several times. Part of the initial problem is that the spreadsheet was also stored with multiple entries per field (i.e. county). They also stored the address as one big field instead of separate fields for A1, A2, City, State and Zip.Leave a comment:
-
Basically, they've been using Excel sheets. A lot of users are tentative with Excel and are petrified of Access. I inherited the database in its current state and they wanted to improve a few things, queries being one of them. I don't disagree it needs an overhaul. A big one. The problem is the last thing they wanted (at least for now) was to have the total on the report. They aren't too keen on having me start over from scratch to revamp it...Leave a comment:
-
lonerjohn started a topic Adding a calculated control to an auto-generated report using input from userin AccessAdding a calculated control to an auto-generated report using input from user
Let me preface this by saying I completely understand and acknowledge that the way this database was built is not efficient. However, my office wants to keep this setup.
I have a reports form (frmReports2) to generate reports based on information selected by the user. frmReports2 also contains a multi-value field list box to choose among specific fields to add to the report. All this works fine. My problem, however, is that 9 of the... -
My problem is I'm not really advanced with Access. I've taught myself what I know so far. I'm inheriting this project in my new office from someone else. I wouldn't even really know where to begin. I'll post it in a new thread, just in case you do accept the challenge.Leave a comment:
-
Yeah, it's a lot more complicated, I'll explain it here, and if it's meant for another thread, I can post it again.
Let me preface this by saying I completely understand and acknowledge that the way this database was built is not efficient. However, my office wants to keep this setup.
I have a reports form (frmReports2) to generate reports based on information selected by the user. One of the command buttons contains...Leave a comment:
-
No worries, I'm just happy it works. Now I just have to figure out the calculated control and my week will be set lolLeave a comment:
-
I commented out the line:
And now it works the way it should.Code:set appAccess = createobject("Access.Application")Leave a comment:
-
That does work, but it opens up a whole new instance of my databaseLeave a comment:
No activity results to display
Leave a comment: