Tables :: Creating Database Of Data Groups
Jul 19, 2013
My challenge is to take about 200 boxes, all of which are stuffed with file folders, and to create a database of their contents. That's all well and good. I made a simple table that listed the record, the date, etc, but I ran into trouble fairly quickly when I came across a folder that had folders inside of it. Basically I need a way to represent the folder structure the way it is in the box that makes sense within Access. What I'm imagining is something like this:
Record 1
Record 2
Record Group:
- Record 1
- Record 2
Record 3
Record 4
More recently though, I'm wondering if don't need to make a whole new table for that set of data. I just don't know how to set up the relationship
View Replies
ADVERTISEMENT
Mar 23, 2008
Hey guys,
OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:
http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg
At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:
If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.
My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so
Any help?
Thanks a lot in advance!
Daniel
View 4 Replies
View Related
Aug 10, 2012
I want a Text Box Query on my form to display the Status, Workshop, Time, Enrolled and Limit. The problem is these values come from two different tables and the Enrolled value comes from a single field that contains the different workshops.
What I mean is: In Table[Attendees] a row contains a customer's Number, First Name, Last Name, Workshop and Phone Number. The workshops vary for each customer so one row on the table could have John Doe attending Cover Letter Writing and the next row could have John Smith attending Resume Writing. What I want is to be able to count the different workshops within the Field[Workshop] and total them and then display the total in a Text Box Query. I have a Text Box Query set up displaying Status, Workshop, Time and Limit as these values all come from Table[Workshops].
So basically I need to Query to also display a result that is the Total for each workshop from Table[Attendees] and display the total for each workshop in a Query with data from Table[Workshops].
Here is a link to an Example Database [URL] ....
I'm trying to avoid putting things on different reports and the like because the people using this are basically computer illiterate and if they have to click a button (no matter how well labeled) they won't do it and the information might as well not exist.
And if there's a better way to do it, I'm all ears. The only thing is, I have to update these workshops month by month. Since they are dynamic, I want to avoid creating separate tables for each workshop.
View 14 Replies
View Related
Aug 21, 2013
Using access 2010; anyway to create an er diagram from an existing database? I have inherited a fairly large database and need to make changes to some field properties and need to find out where these fields are located. I know I can go into tools and run the documenter but doesn't give me an easy format to look at.
View 5 Replies
View Related
Apr 8, 2015
I was asked to create a table within an existing database using these SQL commands;
CREATE TABLE Vehicle (reg_no TEXT(10)
CONSTRAINT VehicleKey PRIMARY KEY,
type TEXT(10),
purchase_date DATETIME,
last_service_date DATETIME, mileage_at_last_service INTEGER);
I was told to save the query and check the new table had the required records in it.
I had huge problems when I tried to save the query and kept getting error messages like: "query must have at least one destination field" and "syntax error in field destination". This was strange since I was sure I typed the query exactly as it had been written on the worksheet.
Having not changed the SQL command at all it eventually worked when I skipped the 'save query" stage and just went to the "run query" stage. I still don't know why it actually worked in the end and why I kept getting error messages.By running the query am I supposed to just click on "RUN" or can I check the information first by looking at datasheet view?
View 6 Replies
View Related
Aug 17, 2014
I am a newbie at Access. I have uploaded an Access 2013 file and an Excel 2013 file to show what I am trying to do.
(I also incorrectly uploaded this question under QUERIES - since marked there as 'Solved")
The Access tables that I have created are meant to mirror the individual fields on the Excel spreadsheet.
On the Excel spreadsheet, I would enter data in the rows as the data becomes available.
The Access file contains the excel fields as tables. Some of the table fields consist of only one field and others have many fields.
I have created Primary Keys in all the tables. But I don't understand the process well at this point in time.
I need to know how to create proper relationships, and then, to create a query or a form to enter new data as it becomes available so that I can keep adding rows of data (query to enter and update data) and subsequently run queries to analyze the data
Need to create the correct relationships and a query that lets me update all the data for new cases, as an individual row (like on an excel spreadsheet) .
View 1 Replies
View Related
Jun 18, 2014
I am creating a table that is a master list of all of my company's product. Each "customer" that we have will always be ordering the same items, but not all of the items that we have available. I need a way to go through the master list and click a yes or no and have that item added to the "customer's list of items on a new table.
I need to create a sublist for each "customer" like individual shopping cats for each customer. These individual lists need to link back to the master list in case of product changes, description changes, and cost changes.
I would like to create a form where the end user can type in a product number, description, or manufacturer number and have that item added to the "customer's" list.
View 3 Replies
View Related
Nov 19, 2013
How do I create a form that will allow me to enter data into three different tables?
I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.
I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.
I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.
View 3 Replies
View Related
Dec 29, 2014
I have a number of groups in a particular database. These are based on the month that they relate to, but they are also split into four separate groups for each month. So for example, I will have four groups for November 2014:
November 2014 Raw Data (which contains linked tables in another database)
November 2014 (which contains the bulk of the queries that are used for processing)
November 2014 Reporting (which are the queries that are used for month end reporting - obviously)
November 2014 Misc (anything that doesn't fit into the above, although there are always queries in these groups)
Now, I have been using this database since 2012, so as you can imagine there are quite a significant number of groups. Adding queries to new groups for each new month is therefore starting to take a while by scrolling down to the bottom... Is there a way to do this by using VBA? It would save a bit of time each month.
View 6 Replies
View Related
Jun 18, 2013
I new to Access, I have used MS Works spreadsheets and database. Im working on a database for a non-profit to enter weekly donations.I have tables below:
1. DonorsT (names...) table
2. FundT (names...) table
3. SubFundT (names...)table
4. DonationT table (for cash, check, checkNumber ext.. )
5. DonationToFundsT table.
The Relation set up:
DonorT DonorID PK (1 to many) DonationT DonorID FK
FundT FundID PK (1 to many) SubFundT FundID FK (so the main Funds can have many SubFunds)
Because one donation can be split to many Funds/SubFunds:
DonationT DonationID PK (1 to many) DonationToFundsT DonationID PK
SubFundT SubFundID PK (1 to many) DonationToFundsT SubFundID PK
My question:
As you can see donations can only be recorded to SubFunds:I can work around this by having the first SubFund name be the MAIN Fund nameBut I was hoping there was a way to enter donation to the main Fund and the SubFundsExample with this set up:
General Fund
General Fund $100.00
Repairs $50.00
Total to General Fund $150.00
What I would like
General Fund $100.00
Repairs $50.00
Total to General Fund: $150.00
Because this data will be entered by people that dont work with Access much Im trying to make it simpler for them.
View 6 Replies
View Related
Dec 3, 2012
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
View 1 Replies
View Related
May 20, 2015
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
View 3 Replies
View Related
Nov 26, 2007
Hi all,
Im trying to write a query so that all data in all but two tables is deleted, not the tables themselves only the data.
I keep getting these errors when im trying to build a suitable query..
COULD NOT DELETE FROM SPECIFIED TABLES
or
TOO MANY FIELDS DEFINED
With any luck i'd like to able to have this query run automatically each day is there any way to do that or do i have to repost in vba section?
Thanks for reading!!
View 3 Replies
View Related
Oct 10, 2013
So I am pulling data from a SQL data base into access and appending it to certain tables. To do this I have 15ish append queries. Is there a way to have them run in a certain order with one command?
View 3 Replies
View Related
Aug 3, 2013
I am preparing a "simple" training database. I want to do this smartly and not duplicate data.
My first table is the list of employees and their information. I have set a primary key (PK) up for each employee.
My second table is a list of training courses - title, description, start/stop time, credits, ... Each of these courses has a unique number (PK).
It's the final database table that is driving me nuts. I want it to show whether a n employee has taken/will take/not planning to take a course. My idea is to have a status field and then wanted to link that with a student number and a course number from the other tables.
In the past I would just have put common data fields StudentName from one table and CourseName from the other into the final database tables as a means of linking them. But I thought/read it's better to use a PK instead. Well when I make the third table up I get all of this Type Mismatch errors.
View 6 Replies
View Related
Aug 15, 2014
I'm exporting data from a database (using a query) to create an excel spreadsheet. I then import said spreadsheet into a new database. I was hitting lots of problems (subscript out of range, violating this that and the other etc etc) which I cleared up and actually imported the data. Well today I cleared down the new database and imported the spreadsheet again and got this.
the contents of fields in 0 records were deleted and 0 records were lost due to key violations.Thereby followed what to do when you get these things happening.Now to me 0 records deleted and 0 records lost means it's all worked. WRONG!! No records were imported at all.
View 5 Replies
View Related
Apr 28, 2014
I've been making small changes here and there to my database in Access 2013. I have left the original database in place and it has acquired many new records from the last month. I'm ready to start using the newly updated design version of the database, but I do not want to have to type all those new records into the new database to get it up-to-date. There has to be a way to import the data from the original database to the newly updated one.
Most, If not all of the fields have the same names, but some of the fields have changed, like to Combo boxes. I removed two or three unneeded fields, and added two or three. I understand the new fields will need to have data enter to them, but the remaining would be redundant and inefficient if I have to reenter all the recent records again.
How do I import the data from the original two tables to the new updated database? I only have two tables and they use a One-to-One relationship in both databases. The table's names are different, but as I mentioned earlier, the fields are mostly the same. Do I need to import into Excel, and then modify the data slightly, and then import into my new database?
View 2 Replies
View Related
Apr 25, 2014
I need to update data in a bunch of tables of a sql server database. The database has 300 tables which I have linked via odbc. I'm hoping there is a simple way, using vba, to loop through my linked sql server tables and determine which of them are views as opposed to tables.
View 1 Replies
View Related
Sep 5, 2014
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
View 10 Replies
View Related
Dec 11, 2012
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
View 6 Replies
View Related
Apr 25, 2006
Hello everyone!
I heard from a number of people that this is the place to come for Microsoft Access related help.
So here is my question and I hope someone can help me understand access a little bit better.
I am trying to create a database for a job I am doing. THere are 700+ convenience stores that we (4 inspectors) inspect every three months.
We have been told to do an ADA compliance survey when we visit these stores.
The ADA checklist that we have is about 78 questions and my manager wants me to create an ADA database for this information.
So I am in need of help on how to create a database that allows me to input the answers of this ADA checklist for each store inspected.
I then would like to be able to pull all that information into a report that I could say of the 700+ stores, 50% passed all questions, 25% passed only questions 1-??, etc. etc.
I can upload the two files: The ADA checklist and our Store list if anyone would like to help me out!
Thanks Much!
View 9 Replies
View Related
May 3, 2007
I am creating a very simple database,
It has 3 tables
People
Employment
Organisation
The people table contains:
surname
given name
email - primary key
phone number
course end year
Employment is the linking table it contains:
Role
start year
end year
employer email - foreign key same as email(above)
org_url - foreign key same as url (below
Organisation
orgname
add1
add2
add3
url - primary key
All I want to do is have three tables - enter a persons details in one form
View 14 Replies
View Related
Apr 25, 2005
Hi I need to create a documentation - help system in my Access database. Can you give some guide lines and tips? General of course... like how to link a help file or something like that...
thanx for the helping hand
View 2 Replies
View Related
Oct 23, 2012
So my current employer has tasked me with creating a new database that we can use to process all of our orders.
Simple things first:
- Database is for processing turkey orders, two types of turkeys can be purchased. Each type must have it's own unique numbering system.
- One table with all the customer Information (Name, address, telephone etc etc)
- One table for "Type 1 Turkeys"
- One table for "Type 2 Turkeys"
- Relationship from "Customers" to "Type" 1 using "Customer ID" as unique identifier.
We need a single form that can be used to see all of the customer information, as well as create an order for either/both type(s) of turkey. My problem is this; I can create a relationship between "customers" and "type 1" and have a form showing all of the customer information and a table allowing for the creation of "type 1" orders, but cannot find a way to have a second table allowing for the creation of "type 2" orders. If I create a second relationship between Customers and Type 2 I am forced to choose which I want to be displayed.
A screen shot of the current database. The current relationships just allow for a drop down selection under each orders table for selecting products:
View 7 Replies
View Related
May 31, 2013
I am assisting my employer by combining two databases into one. Both databases have the same field "structure" but the data differs. When creating my append query.
View 3 Replies
View Related
May 25, 2005
I currently have a database on my hd and four other ppl have a copy on theirs. (We are in different physical locations which makes it very slow to put the database on our server).
Currently eveyrone is sending me files that i upload to my copy of the database so i have all of their data.
the obvious setbacks to this is that the data is not live and some of the changes might be missed etc.
i would like to move this and house the tables on my company's intranet
what would i need to do to accomplish this?
View 1 Replies
View Related
