Am I Heading For Trouble? I've Never Had So Many One-to-one Before...
Jan 17, 2008
I have been asked to redesign a database that tracks a huge number of data points. These are projects and the original table had over 100 fields. I have managed to separate them to related tables in an attempt at normalization. They are:
tbl_workorders (main project info)
tbl_services (services ordered)
tbl_contacts (internal company contacts)
tbl_customers (customer information)
tbl_project_dates (milestones of project)
Now this is different from other databases I've designed because all of the tables need a one-to-one relationship with the main table (tbl_workorders).
Am I heading for trouble with so many tables existing in a one-to-one? Also, The table tbl_workorders has its primary key as an autonumber. I want any new order on this to create matching foreign keys on all the other tables...I assume this should be handled since I have enforced referential integrity with cascade on update/delete for all the other tables.
Thanks for your feedback!
View Replies
ADVERTISEMENT
Oct 23, 2006
Hi all, this is my first post.
i have created a simple access database for keeping student and attendance record.
student table fileds are:
studentId - primary key
forname
surename
dob
gender
accademic year
attendance table fields:
studentid - primary key
date - primary key
attendance (yes/no boolean field)
paid (yes/no boolean filed)
this database is just ment to keep records of students attending at particualr dates.
for example if attendance table cotain records like:
http://www.crazyanime.pwp.blueyonder.co.uk/table.JPG
for the form layout, what i want to do is
http://www.crazyanime.pwp.blueyonder.co.uk/form.JPG
i want this to be editable. how would i do this using access form, or do i have to wrtite VBA code
PLUS i want the form to automatially have new records when i add for example a student, with ID 10011 OR if i add new records for a different date say 11/11/2006, then i want that to be viewd on the form just like the 21/09/2006 and the 04/11/2006.
please help :) been looking for a solution for long time.
thanks
View 4 Replies
View Related
Dec 7, 2005
Being an amateur at Access, I've only recently realized that you can force column headings for crosstab queries, by typing those headings into the properties of the field selected as the column. But can you do the same for ROWS?
My data concerns patients of a specific type and the times of day they show up at our A&E (ER). I've grouped these by hour and now I want to crosstab them by day of week. But if there aren't any between say 3am and 5am, those hours don't show in the query, which is screwing up a pre-formatted report I have.
I've found some assistance on other threads.....
http://www.access-programmers.co.uk/forums/showthread.php?t=92471
http://www.access-programmers.co.uk/forums/showthread.php?t=83820
.....but to be honest I didn't really follow it.
Is there an easy solution? If not, it's not a huge problem and I can "fudge" it once a month, offensive as that must sound to you professional types!
Thanks as always.
Pat
View 3 Replies
View Related
Feb 20, 2006
Excel sheets:
Item Item Desc Price Price Out Date
11040 MIRR SHUTTERED 38.5 69.99
P.O. # Status
52334 280
53074 280
53075 280
11041 MIRR SHUTTERED 38.5 69.99
P.O. # Status
52334 280
53074 280
53075 280
And I want to make it like:
11040 MIRR SHUTTERED 38.5 69.99 52334 280
11040 MIRR SHUTTERED 38.5 69.99 53074 280
View 2 Replies
View Related
Sep 30, 2013
I have below vba code in a sub that opens word application and generates the data from a table based on criteria provided thru a form.
These lines are smoothly working..
But I need to place some headings in first line or second line of the document and then to start the table information to appear in the doc.
I tried to place my company name below way (see bold lines);
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim I As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM T_CustomerMaster WHERE SALESMANCODE='" & Forms!F_ReportMaster!TxtRepCode & "' order by custcode;")
WordSetup
doc.Tables.Add Range:=doc.Range, numrows:=1, numcolumns:=5
'Trying to place main heading
'doc.Range(1, 0).Text = "fsdafds"
[Code] ....
But the table starting from column 1 and cell 1 till data ends up.
Finally I converted my trial lines to remark as it is not working at all.
View 8 Replies
View Related
Jan 20, 2014
When using the Forms wizard for a tabular form, is there ANY purpose for the Heading of the Forms to be OVER the Field headings?
I find it annoying, but maybe it is because I don't understand WHY I have to resize it every time, before I can use my field headings on the form
View 5 Replies
View Related
Mar 23, 2013
I have a cross tab query which give result like this
Date No. BoxItem1 BoxItem2 Item1 Item2
1-2-12 1 15 15 10 15
1-2-12 2 10 10
1-2-12 1 15 15
1-2-12 1 10 5 5 1
I need to BoxItem1 come after item1 and also col heading only box.Is it possible or not?
View 1 Replies
View Related
Aug 23, 2005
Hi,
I have a crosstab query that groups by week to obtain columns for monday to sunday. Is there a way to obtain the actual dates that these columns represent?
Any help would be much appreciated.
View 1 Replies
View Related
Feb 7, 2006
Hi,
Not able to add more column heading in cross tab query.
I tried to change the query properties to add more column headings as given below.
In the query's Design view, right-click up in the area where your tables are shown and choose "Properties" from the right-click menu. The 3rd line down is for Column names. Enter what you need there.
Evn after doing it. i am not able to .
Whn I try to save or view the datasheet it says. to create a crosstab query u need to have one or more row headin one column headin and one value.
please help. its ver urgent.
thanks in adv..
View 1 Replies
View Related
Oct 7, 2007
I have a cross tab query for which I want to display the dayes in mmm-yy format. I am grappling with the problem that this is essentially a string, so gets sorted alphanumerically.
Having read around related forums I have tried basing the XTab on a select query, which is sorted by the conventialal date, but also has another field that gives a date using Format([DateAdded],"mmm-yy").
Trouble is, Access only lets me bring across 1 column heading, so I must sort on the column I display, and this doesn't solve the problem.
The coulmn headings are not be fixed (i.e. "Jan";"Feb" etc) as the months are financial year months (April - March).
I can get this too work by using yyyy/mm, but my board of directors are not happy with this format.
Any ideas. I would be open to re-querying my Xtab from another query of from a report if that worked.
Cheers
Mike
View 4 Replies
View Related
Feb 25, 2015
I am importing different excel sheets into Access dB using a file dialog. The importing works fine however, I would like to rename the tables once they are imported to the name of the first column heading. Where exactly would I ad the name change at in this code?
#' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(StrFileName, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
[Code] .....
View 2 Replies
View Related
Apr 2, 2013
I have a report that starts with an unbound form [Form1] which I need for the parameter inputs. Date1, Date2 and BillingMonth, all three are unbound controls. Date1 and Date2 are the criteria for the dates of billing and they work fine, that is, the query looks them up and the query runs fine. But I want to use the unbound control of BillingMonth to populate an unbound control in the report heading.
The control source is set as =[Forms]![Form1].[BillingMonth]; it works fine if the underlying [Form1] is still open. There is a command button on [Form1] that opens the report and then closes [Form1] since it is a pop-up form that is only used to get the criteria for the underling query; I don't want it to remain open when I run the report. However, the [BillingMonth] does not appear on the report heading. Just #Name?, which I assume means that since [Form1] is no longer open, it can't find the control [BillingMonth].
So, how can I get the [BillingMonth] to appear on the report heading?
View 3 Replies
View Related
Sep 10, 2014
I am coming back to an Access 2007 database that I put together some time ago. I need to simply delete a column and its associated heading in a report and I can't remember how to do it!!
I have attached a few screen shots.
001.jpg is of the actual report and 002.jpg is what the report looks like in Design view. If I try to use Layout view it displays the message "Layout View is Unavailable for this report".
View 14 Replies
View Related
Aug 11, 2015
File import with special heading
ABC. or 123.
First time the file import, system will ignor the "full stop" into the table. But next time when I try to import to update the table, system error can not identify the heading.
View 2 Replies
View Related
Sep 23, 2015
I am unsure of how to choose my row heading when I use the create table wizard. It gives me the option to use the first row but my row heading is actually row 17.
View 6 Replies
View Related
Nov 30, 2006
Im running a query and normally there is only a field-name in heading. I have
multiple tables with equal field names. Now I want to get table names in
heading too (Tablename.Fieldname) so I can make difference between fields on each tables when previewing query. Is this possible in access? I don't want to change all field-names manually, I know it's possible and done easily, but there are almost hundred fieldnames...
Im finding a solution. Help me please. Thanks :eek:
- Roger
--
Have a nice day.
View 1 Replies
View Related
Apr 2, 2015
I have a subreport and would like to have a header that repeats on each new page if it extends to multiple pages.I tried the suggestion I have seen to create a dummy group header based on an expression of =1 and set the group header "Repeat Section" property to "Yes".header now repeats before every record, instead of just once at the top of each new page.
View 1 Replies
View Related
Nov 20, 2014
I have fields on the main menu where folks put in a start and end date prior to running a report. I'm doing a monthly report that pulls current month, and two prior months of data. For example, the user selects a date range of 10/1/14 to 10/31/14 the report will show August, September and October results.
I need the column headings to change each month depending on the date range they select to run the report so in November they will pull 11/1/14 to 11/30/14 and get September, October and November.
The headings for the columns would then adjust.
I put an unbound text box with the formula as follows in the control source of the report page header to pull the month that is equal to the current month minus two (I'm just showing one of the column headings formula):
=Format([Forms]![Main Menu]![EndDate]-2,"mmmm yyyy")
This is not working. It's pulling the current month which I have set to run for October so it should show August 2014.
View 3 Replies
View Related
Jul 3, 2014
I'm trying to run a query which fetches only the last 2 years of data for a given region from a table with several years worth of data (there are year, region, sector and rank columns among others). The region is passed into the query from a combobox from Form1.
The first problem was that some regions have up-to-date data and some not so much e.g. for Europe the "last 2 years" mean 2012,2013 for Asia its 2011,2012. In order to deal with this I've created a crosstab query which works well except for one thing - because the columns are dynamic (dependant on the region) the column headings change as well.
And here comes my question, how can i fix the column names to be e.g."Current Year" and "Prior Year" independent of the query fetching 2012,2013 or 2009,2010? I've tied different things with PIVOT... IN ... but with no luck.
Here's the sql for the query:
Code:
PARAMETERS [Forms]![Form1]![cmbRegion] Text ( 255 );
TRANSFORM min(DataTable.Rank)
SELECT DataTable.Region, DataTable.RegionalSector,
FROM DataTable
WHERE (((DataTable.Region)=Forms!Form1!cmbRegion))
[Code] ....
View 7 Replies
View Related
May 3, 2012
I have created a combo box search for my form based on three categories, 'Student Name', 'Nationality', 'Age' using the wizard. When I click on my combo box in form view, I see 'Alex', 'UK', '19' and 'Stephen', 'Sweden', '22' in the dropdown list, but I do not see the headings 'Student Name', 'Nationality', 'Age' as the first item on the list.
View 1 Replies
View Related
Nov 21, 2011
I need to create a report that has three labels as a heading on one level and then it will display the rest of the headings on another level.
View 2 Replies
View Related
Apr 30, 2015
I was wondering how to do a crosstab query and have to column headings
I need the Organization Number and the Org name..so something like this
4005 4010
Office of HQ Office of Accounting
Is this possible?
View 2 Replies
View Related
Jan 6, 2005
Hi,
I'm trying to get a crosstab to output some columns that dont have data. e.g. a feedback survey with no low scores.
Everytime I put something in the Column Headings property the query becomes "too complex" and won't complete. I have tried just putting "Question" which is a Column that should always output.
Any ideas?
Thanks,
Darren H
View 1 Replies
View Related
Jun 20, 2005
Hello. Recently posted for advice on building an access database on health service training. I had a lot of help but I suppose I haven't really grasped relationships yet. My database looks like this:
tb_staff containing staff_id, first_name, last_name, base, dept_ward, job_title
tb_courses containing course_id and course
tb_training_done containing staff_id, course_id, date_trained, trainer, venue
I have 1:M tb_staff to tb_training_done and the tb_training_done is linked to the tb_courses 1:1 - not sure if this is right but it's the only way access will do it.
Anyway, my idea was to use a form and subform to enter training done but after setting up the lookup it won't allow duplicate courses i.e. same course done each year. Can anyone explain how the lookup works? Where are the details of courses done stored?
Hope that's not too vague?
View 2 Replies
View Related
Aug 6, 2007
im having a bit of trouble generating a field in one of my queries. i think its fairly simple, all i want to do is have the field in my query to show a date, that is just calculated by subtracting 5 days from the current day. i have something that looks like =Now()-5 but that doesnt work obviously. so im just asking whats the imput going to look like if you want to subtract 5 days from a date? oh and im using short dates for the date inputs
View 2 Replies
View Related
Sep 13, 2004
I am trying to build a report that lists all call in's and all tardies on the same sheet. They are two different tables, that I made queries out of because of calculation fields. Do you know how I can include all the needed data in the same report?
What I'm getting when I try is either all call-in's for each name that has tardies or vice versa. I need to see all data on one report
The reason I didn't just put all the data in one table was because we track call-in's by occurence, but we track tardies by pay period. So each call in is logged in the call-in table with the date it happened on, and the tardies are logged in by pay period, so only the total is stored in the field.
Do you have any ideas? I'm really stuck and I've been workin' on it for three days now and I don't think I'm going to find the solution by myself. Sorry. Thanks!
KellyJo
View 5 Replies
View Related
