I am very new with access database. Want to create table with Dates & DayName. For example, tblYear2015 with all dates from 1-Jan-2015 to 31-Dec-2015 in FirstColumn and DayName in second column as per date of First Column. DayName Should be entered automatically as per date entered in first column.
View RepliesHow to create a table in MS Office. Current table has name in first column and start & finish dates in other two columns. It is necessary to create a table in MS Access which has name in the first column and validation dates in the second column. For Example:
currently
1. White 16 xxxx19
2. Black 1 xxxx 5
required
1. White 17
1. White 18
2. Black 2
2. Black 3
2. Black 4
I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).
Currently, I setup the query to pull info from the form field like this:
DateField: [Forms]![frmmain]![DateField]
However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
Hi All
I am trying to create a database for estimating manhours on projects. There is a setup table with two columns: "Project" and "Time Period". The database is supposed to allocate manhours to tasks on projects, which is used in estimating man hour costs and also forecasting labour resource requirements.
For example, say the database is used for "Project X" and "Project Y" projects, with Project X running from Jan to March and Project Y running from Aug to Dec. The setup table would look like this:
PROJECT TIME PERIOD
Project X Jan
Project X Feb
Project X March
Project Y Aug
Project Y Sept
Project Y Oct
Project Y Nov
Project Y Dec
From this table, I need to automatically create a new table for each unique project that allows man power to be allocated to each time period. Following on with the example, there are two unique projects, so two tables
need to be automatically created with column headings as per below:
Project X table:
FUNCTION PERSON JAN FEB MARCH
Project Y table:
FUNCTION PERSON AUG SEPT OCT NOV DEC
"Function" is a description of a role, for example engineer. "Person" is the individual undertaking that role, for example Bob Jane. Then in each time period a number between 0 and 1 would be entered corresponding to how much time (0 is no time, 1 is full time) that person would be spending on that function for that time period. Then the table would be populated with many functions and people in this manner.
So the number of tables automatically created depends on how many unique projects there are in the "Project" field of the setup table. The number of columns in these tables will be atleast two: "Function" and "Person",
plus another column for each time period defined for that project.
Perhaps something can be done with a make-table query, but all I can get that to do is copy data from one table straight into another table.
Any help would be greatly appreciated.
Thankyou in advance.
Joey
Hi, i currently have three tables.
Policy table: Policy number(key),date and fund(4 possible strings of four letters)
Last Price: date, fund, last price
Next price: date, fund, next price
What would i have to do to make a table that has a policy number, date, fund, last price and next price?
Thanks
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
I am making a basic hospital management system in Access 2013.I have two tables named "Bed" and "Receipt". Bed(BedID,AssignedDate,PatientID,DischargeDate,Bed Charges) Reciept(ReceiptID,PatientID,BedCharges) I want to calculate "BedCharges" by calculating the number of days using "AssignedDate" and "DischargeDate" and then multiplying with a constant amount of charges per day. Also the BedCharges calculated in "Bed" Table also needs to be in the "Receipt" table. How can I count the number of days and then calculate the "BedCharges" in both the tables?
View 8 Replies View RelatedI'm new to MS Access, indeed database design as a whole.
I have been tasked with creating an issues logging database and am having some issues...
I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.
Hello,
I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks
Richard
Hi,
I have two tables:
Table A provides total sales volume of the UK shoe market from 2000-2007.
Table B provides the sales volume of different shoe manufacturers from 2000-07.
I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).
Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).
Thank you.
Is there a way of creating a log to capture changes in a table by users?
In a current database that I'm taking over, there was a lot of records deleted from the main table and now I need to figure out what happened.
So, if I would be able to have a log that records all the actions taken by users, I could at least see who did what.
I have a rotating number of tables that are created from excel spreadsheets that are imported. The Tables will change, but when they are there I need to be able to create a query that will merge them all together so I can run one query against all the tables. In SQL I know you can use a * to say Select *
From Table_1
Is there a syntax for the From portion so I can say:
Select *
From * (AKA all the tables in the Database)?
I have searched for a wildcard for the FROM statement that works like in the select statement but have been unsuccessful at finding an answer. Can anybody help? I'd list the Tables in the from Statement but there are 266 of them. Unless someone knows how to say:
Select *
From All tables in a folder with 266 excel spreadsheets
Thanks for your help
:)
I am a newbie when it comes to Access and ASP but I am trying. I am in the need for some help.
I have a large table in Access 2003 and need to break it into smaller tables (not using a query) where the fields are the same except each of the smaller, new tables will hold info for a specific person. These smaller tables would need tro be linked to the larger.
Is there an easy way to do this? I need to keep it in tables due to the ASP software I use to generate the asp pages.
Is this possible?
My goal is for each user to be able to only view/edit their data and for the large table to reflect any changes made to the smaller table.
Thanks,
Dan
Table has a relationship with master table. Joined on TractID primary table - auto number, TractID child table - number. This works as it should when adding a new record.
What I am trying to do is create a new key for each new record added in the child table with an ID that looks like this: TractID.A, TractID.B, TractID.C etc. for each new record added in the child table. if so where do I look, how to accomplish it?
I am wondering if it is possible to automatically create a new field in one table whenever a new record is entered in another table. The name of the field would be the primary key entry of that new record.
I have one table (table 1) in which each record corresponds to a particular mouse with a unique ID number, and each field is the ID number of a particular genetic marker. The table overall shows what genotype (+ or -) a mouse has at each marker.
In the other table (table 2) the primary key of each record is the ID number of a genetic marker, and the fields are several different bits of information about the marker (e.g. what chromosome it's on, its location on the chomosome, etc.)
I would like to have it set up so that if I enter a new genetic marker in table 2 a field named after its marker ID will automatically appear in table 1. Is there a way to do this?
I'm trying to create a table with an entry for month/day, no year. I want to create a report that can will sort the birthdays based on month in ascending order.
I'm currently using the text field to put in, "10/30" or "1/3" but when I try to order the dates, instead of "1,2,3,4,5,6..." I get "1, 10, 11, 12, 2, 3.." etc.
I created two tables, let's refer to them as Cars (VW, BMW and Audi) and Colours (White, Black and Grey).
Is it possible to create another table based on these tables - i.e. in the new table the rows will be the Cars and the columns the Colours as such:
White
Black
Grey
VW
BMW
Audi
And should I enter another Car or Colour in one of the first mentioned tables, then I would like this "new" table to update automatically. For example, if I have a new Car (say, Merc), then I would like the "new" table to update to the following:
White
Black
Grey
VW
BMW
Audi
Merc
I think what I want is:
1 table(1): record of people & contact details
1 table(2): list of events with check box's with the names of people from the other table
1 report: listing how many events people have attended.
When I add a new person to table 1 I want a field to be added to table 2 in the form of a checkbox, also when I delete this person I want this field to be deleted in table 2.how to make this an automated process.
I am trying to create a table which would include financial data, by year, by client. For example:
Client #1
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Client #2
2009 2010 2011
Revenue 5000 10000 1200
Expenses 2000 1000 700
Net Income 3000 9000 500
Etc.
Should I have headers as such:
Client #, 2009Revenue, 2009Expenses, 2009NetIncome, 2010Revenue, 2010Expenses, 2010NetIncome, 2011Revenue, 2011Expenses, 2011NetIncome
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example:
table1
01/01/2014
15/01/2014
17/01/2014
30/01/2014
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
Explanation:
Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates)
Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2
Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
Is it possible to define 2 dates and create a record for each day in between? I am creating a "sick occurence" database. I want to define the first day the individual calls off sick and the day they will return, then calculate how many sick days they will be using. However, I need to take into consideration any relief days or assigned time off.
Basically, I want to be able to assign what is going on everyday between these 2 defined dates. Be it, holiday, annual leave, personal leave, sick leave, or regular relief. If further clarification is needed, I can go more in depth.
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
I managed to create a report by user using a combo box. When an user selects a name from the combo box, it generates a report showing all the records by that name.
However, now I need to be able to generate a report as above but between 2 dates. How??
I am having two sets of criteria.
1) by user
2) between 2 dates
Can this be done?
I have almost no experience of VBA outside of working a little with codes generated for me like docmd etc.
I am trying to create a database for resourcing staff.
I have a form where the user inputs a start date and an end date which updates a table.
I have a query that works out the number of working days between those dates.
I have a table with all the working dates between now and 2016
I want to put a duplicate record button on the form (I can do that bit)
The tricky part is I want it to create a new record for each date in the dates table between the start and end dates.
Example 23/06/2014-18/07/2014 = 20 working days. I want to end up with 20 records with start dates as below and the same end date.
23/06/2014, 24/06/2014,25/06/2014,26/06/2014,27/06/2014,30/06/2014,01/07/2014,02/07/2014,03/07/2014,04/06/2014,07/07/2014 etc up to 18/07
I have a table that has the following:
ID
Program Year
Program
Start Date
Midpoint Date
Internship Date
End Date
I have successfully created queries to create reports showing all Start Dates by Month no matter what program and similar reports for all the other date queries. What I need it to do is list everything happening within a month and sort them by category. How do I do that? I've attached the database so you can see my queries as well as the main switchboard reports associated.