How do I make the reports show in relations?
I'm (as said before) totally new to everything that has to do with programming, and therefore I work in the visual way, and thus I need to have the reports shown in relations in order to work with it...
Any ideas?
Hi,
I've split a database and the backend relationships are still intact but the front end they are not and it looks like this is causing a problem. Is this usual?
After designing the database that mean after creating all the tables and when pressed on relations buttuon it shows a diagram with relations established. Is this a kind of ERD. in this it shows relations between entities so can we call that an ERD?
View 3 Replies View RelatedHi there,
I'm a bit stuck on this but it should be pretty easy.
Say I have a table secondHandCar - this contains infromation on the makeID (eg 123), price, number of doors. Now each car can have a number of images, this number isn't set so I have an images table...
tbl_secondHandCar
makeID - key
price
number of doors
tbl_images
makeID - key
Image (ole, binary whatever)
Now clearly I have a problem: the images table is going to have many makeIDs with different images. I've just got stuck here - do I need an image id or something? But then my make ID will still repeat in tbl_images.
Thanks for the help :rolleyes:
Hi,
I'm trying to build a mdb which holds customer details, which insurance they have, who sold them this and which Insurance company provided this.
The relations I have to make are driving me nuts !
When I try to enter a new customer in a Form, there is a advisor added, the package the customer wants is added. But this shouldn't happen for the advisor and the package allready exists !
Who can help me with the proper relations ?
Attached is screendump of the current relations.
Thanks a lot !
I have a DB and for some reason queries aren't working properly. My guess is the table relationships I made. If anyone else has an idea on changing my relationships so that all forms and queries will work to thier full potential please give my DB a look.
Any Help will be greatly Appreciated
18338
Hi,
I am creating some table for a database I want to use.
I had the following tables:
[Organisation]
orgOrganisationID (primary key)
orgName
orgPostalcode
orgStreet
orgCity
orgCountry
[Contacts]
coContactID (primary key)
coSurename
coFirstname
coFysicPostalcode
coFysicStreet
coFysicAdditions
coPostPostalcode
coPostStreet
coPostAdditions
coTel
coFax
coEmail
[Postalcodes]
poPostalcode (primary key)
poPostalCity
poPostalCountry
I have two postalcodes in the Contacts table. I can't create two one-on-many relations from the Postalcodecodes table to the Contacts table.
So I thougt I split up the contacts table and create a one-on-one relationship between the two new tables.
So it would look like this:
ContactsGeneral
cgGeneralContactID (primary key, autonumbering)
cgSurename
cgFirstname
cgFysicPostalcode
cgFysicStreet
cgFysicAdditions
cgTel
cgFax
cgEmail
ContactsPost
cpPostContactsID (primary key, numeric)
cpPostPostalcode
cpPostStreet
cpPostAdditions
I would create a one-on-one relationship between coGeneralContactID and coPostContactsID. And now I can create my one-on-many relationships between from the Postalcodes table.
1) Is this clever?
2) Should both these fields have a key?
3) I set coGeneralContactID to autonumbering and coPostContactsID to numeric. is this also correct.
4) I also use prefixes before the fieldnames 'cp-' for 'ContactsPost' is this naming convention ok? I think it makes programming easier. But are there condingstandards for that in Access/VBA?
Thanks,
Jayke
Access user,
:confused:
I am looking how to generate a relation in one database between records of the same field type.
f.i. you have a database where you try to keep track of your parts.
You order a part1 but they deliver an identical part but from another brand so it has another part number.
You will store it under his correct partnumber being part2.
Due to the fact you still have stock of part1 and just recieved part2, now you will run out of stock when part1 + part2 reach zero stock.
In order to be able to monitor this you must be able to generate a relation between part1 and part2 showing that that parts are fully interchangeble.
How can you realise this in access?? Or is this impossible?
Also would it be possible to have for a certain part number multiple relations?
f.i. parts are fully interchangeble
restricted interchangeable
one way interchangeable
Thanks in advance
Hi all,
I built a database and I was wondering if anyone has any suggestions on how to normalize my DB. I think my table relations are wrong.
I'll post my DB for you people to view.
18354
Any Help will be appreciated
Can anyone shed light on why my Database seems to have stopped working so far as I've a combo box on a main form, which provides one half of a PK/FK relationship. The subform displays one or more records for this value. However, I've just entered the same 'value' (string) for an entry in the combo box as another, which shouldn't display any records in the subform yet - but it displays the records of the first combo 'entry'. This important thing is that the key's are all unique (I've doubled checked this), so why does it seem to not work?
Please.
I have 3 tables Metrics, FixedExpenses, Variable Expenses, I want them all to have a date in a form, and a subform for all the records to be added. I want to be able to do this without having 3 seperate date tables linked to the other three tables. I am not sure how to do this correctly. (I want to be able to see all the records for a certain day in these forms.)
View 1 Replies View RelatedI have a table named [Brand] and several tables named [Brand@@@Models], one for each field in the Brand table.I want each field from the [Brand] table to be related to it's specific Model table. I thought like that so I don't have a Brand#1 related to Brand#2 models, etc.. I didn't want to have all the models for all the brands in a single table, was I right to think like this?
View 6 Replies View RelatedI have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not bSaveClicked Then
MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation
Cancel = True
[code]...
I am editing a database that provides the option of creating custom reports, where the user can input a date range of their choice and receive aggregate data for that time frame. Although all of the numbers in the report are correct, I am having trouble with a chart that I inserted into the report.
Specifically, if the date range requested spans 2 calendar years (i.e. April 2014 through January 2015), the data for January 2015 appears at the beginning of the year (so the chart x-axis is for Jan through Dec, and the Jan 2015 data is showing up in Jan (as if it was 2014, not the end of the given range in 2015). When I try with smaller time frames within a calendar year, it adjusts just fine (i.e. shrinking the window so just March-May is displayed on the graph).
How to adjust the axis so that it properly records the data range- so that it would start the axis with April and end in January, for example?
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies View RelatedI am still trying to get a hang of development in access 2010.
I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.
How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.
I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.
I am having issues figuring out how to get it to pass them to a query or filter the reports.
I have tried many different examples and nothing seems to work.
I print a report onto labels (Avery 5960: 3 columns of 10 labels on a letter sized piece of paper). The report and labels print fine BUT...
I need physical page breaks between certain sections. When I click on "keep whole group together on one page" in the "group by" section of the report, it starts a new COLUMN of labels (which Access sees as a new page), but does not force an actual new piece of paper.
How can I force a real page break?
I have been tasked with creating a database to log employee suggestions and then automatically present reports around the site. I have developed the database and it works OK and can output *.PDF reports, but I'm having problems with how to display these reports. My idea was to output the reports automatically as *.TIFF or *.JPG files and have them stored in a folder that the My Pictures screensaver uses. The reports (in fact, any site report stored in this folder) can then be shown on any screen dotted around the site when it goes into screensaver mode. However I can't find any way to output/convert to a picture file.
The other option is to create a webpage that rotates through the saved PDFs, but this isn't ideal as the PC users will have to load the webpage to display the reports.
I have date picker which works correctly in form. When I put that form as subform to reports header calendar shows up but after selecting date on calendar textbox stays blank. Format of textbox is Short Date, Show date picker property is For dates.
View 1 Replies View RelatedI have a database of high-school football players, and I am looking to print out single page reports (or forms) that will show detail from several tables and queries. This will act as their resume when they visit schools on recruiting visits. The reason for needing query items, is that I have developed queries that return the most up to date height, weight, 40 time etc., and that single most up to date number is what should print, not the entire table. When I try to build a report it will let me bring in multiple tables, but not queries.
View 2 Replies View RelatedI have a continuous form that displays a list of invoices (frmInvoiceList) based on an adjustable filter contained within the form. I have the filters working the way that I want them through VBa and I have a button on each record to open a report (rptInvoice). Inside that report, I have some controls to "print", "email", and "export to PDF". Again all that works just the way I want. The Form and the Report are based on a different query and each has an InvoiceID field to link.
What I am trying to do is put the Print, Email, and Export buttons on the main form which would perform the appropriate action for all the records that are displayed on the form. I've been able to get the Print function to work to a degree. It will print all the records, but it changes the background colors based on the alternating records. When I go to Email or Export, it creates a single file with multiple pages and each page is a new record, again with the alternating background colors.
Ideally, I would like to have a separate file exported for each record that can be renamed and archived separately. I'm not so concerned with the email function but if it would be possible to generate a separate report for each invoice, then pull the appropriate email address for the record, that would be really nice.
I've tried some "for" and "do" loops that I found through some Googling but none of the samples ended up working like I wanted.
I am trying to create a P&L statement in access. I know what I want to come out at the end. I am just starting to play with access and having trouble getting what I want to come out of it.
On the sales side I have a query that gathers all the revenue sources and calculates a total for each date. I then use a second query to just take out the data I want for the P&L report. I created a sub-report that displays the data I want. I use the grouping and grand total features to get the total into the report footer. So far so good.
On the expense side I created a form of a query to manage the one to many relationship to capture the data for expenses (one purchase with many line items). I created a query based of this query to get the relevant data for my expense sub-report. I created the sub-report and got everything looking and calculating the way I want it to. I use the same grouping and grand total features to display the data in the report footer. Still good.
I created a new main parent report with the two sub-reports (sales & expenses) on it and even was able to pull the totals from the sub-reports into the main (so currently the subtotals of the two sub-reports are displayed twice). Now when I try to use the textboxes I used to pull the sub-report totals into the main report to perform additional calculations (sales - expenses) I get #error. I have tried different things and gotten ?name.
Control source for the two textbox controls on the main that display correctly, but don't let me do any further calculations.
=[rptP&LExpensesOverview]![AccessTotalsAmount]
=[rptP&LSalesOverview]![AccessTotalsTotal Sales]
To do the subtraction I have tried using the references above, as well as just using the names of the unbound text boxes in the report that bring the totals into the main report.
As a work around, I tried to build one query with all the data from sales and expenses, but can't "filter" based on date and get the data I want in the query results because the two sets of data are not necessarily related. I either get a long list of records, or no records (I am currently only playing with about 5 days of data).
I am using a form with command buttons on it to set the criteria in my query to run the report.
Example : cmdQ1 'Command Button
PHP Code:
'1st Quarterly Report.
BeginDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
EndDate = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 4, 0)
I managed to get this far but need to continue on with 2,3, and 4th quarters.
I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:
stDocName = "rptFinal"
DoCmd.OpenReport stDocName, acViewReport
In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:
[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date]
and also,
[Forms]![FrmDate]![Enter Type of Audit]
On each subreports On Load Event, I have added:
Private Sub Report_Load()
Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#"
Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#"
End Sub
and I've set the Filter On Load property to: Yes..I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, I have 6 blank subreports in report Preview. The headers are showing up but none of the data.
I have created a report which contains within it multiple sub-reports, which I use to generate a document for management meetings on a bi-weekly basis.
For each of these I have the subreports filtered to a unique number for consideration that period e.g. LIKE "88/00039" which relates to my data.
In order to change this I have to manually update each of the filter commands within the subreports but I assume there must be an easier 'catch-all' method of achieving this?
Ideally I'd be looking for a command prompt so I could enter just the number sequence e.g. "88/00040" and then enter this (via a corresponding macro or similar) to update the filter commands.