I have been trying to design an Access 2013 desktop database to report on user access to shared folders within the company I work for. Acess to folders is granted by users being added to a permission group and groups being added to folders.
What I want is to be able to query the data and find out which users have access to which folders. My tables are currently:
tblGroups GroupID: PK Autonumber GroupName: Short Text GroupType: Lookup (Permission; Distribution; Mailbox Sharing)
[Code]....
If I do have to have a junction table is there anyway it can autopopulate from the other data entered?
I have a report, based on two tables which I have combined into a query.
The first table is the invoice date & ref, and the second table is the invoice amounts & descriptions, which could be more than one.
I have based the report on the underlying query, and I want all the many invoice & amounts to appear on one invoice, but I get one invoice for each of the many sides of the relationship.
I have a related key field in each table, with a common ref and referential integrity and cascade update set, but it still produces one invoice rather than a combined.
We have customers, cars availible and the date and time and location etc... of when to pick up the customer
So 4 tables i have so far:
cars customers Bookings Drivers
a field in Bookings known as customerID is joint to the customers table, field CustomerID. a field in the Bookings known as DriverID is joint to the Drivers table, field DriverID a field in Bookings known as CarID is joint to the cars table, field CarID
I am wondering, does this sound ok to you? i am not great with relationships but i am improving. does this sound ok?
Another Q is, i need to know what times of bookings are availible. What is the best way of going about this?
hi, im a a level it student and my current coursework is making a new program/system for access im making a new order form for the shop where i work and i want to link sundries on a diff order form to sundries on the main order form so it prints it all off on one sheet, basically i want to know how i can get the realtionship between ID number of the order, and the sundrie to link together, and if its possible at all, if its not ill have to change my project slightly which i dont mind, just wondered if any one could help me out a little thanks!:p
I'm just putting a database together, and I'm having a relationship "issue". It's fairly simple right now, but I'm not sure if you can do what I am trying to do..
Basically I have, so far, 3 tables. Each one has a primary key "Serial #".
There's a "Systems" table, a "Parts" table, and a "cash flow" table. I have a link from
Systems - Cash Flow Parts - Cash flow Both links are set to enforce referential integrity (with cascade update/delete enabled as well).
What I want it to do is work so that I can add new values to either the Systems or Parts table, with their own unique "Serial #" code, and then be able to add the value to the cash flow table.
Currently, I can add items to the "Systems" table, and then add the corrosponding entry into CashFlow, and that's fine. If I try and input an invalid entry, it will tell me that the appropriate entry does not exist in "Systems".
However, I cannot add anything to "Parts", since it gives the error that the appropriate entry does not exist in CashFlow. Obviously, I need to make it in Parts first, and even if I wanted to make it in CashFlow first, it wouldn't let me (entry does not exist in "Systems").
I have tried fiddling with the relationships, but I get other problems like not being able to add items to "Systems", etc. If I try and drag "Parts" to "CashFlow" the other way (in the relationship), it tells me that I cannot do that because it violates the integrity rules.
I can see WHAT the problem is, and I know WHY it's doing it, I just don't know how to fix it.
Basically, I want the field "Serial #" in the table "Cash Flow" to be able to draw from the field "Serial #" in either Parts or Systems, but reject anything else (in other words, incorrect serial numbers, since the item shouldn't be in the cash flow table until it has been registered as an item in either "Parts" or "Systems").
I am having a bit of troubles in my design conceptualization i believe. I am building a database to keep track of a companies liabilities....therefore i have several liabilities that all belong to one company. I have one table that is called 'client information' that lists the corporation name which i have identified as the primary key. My other table is called 'liabilites' and i also have a corporation field in that table but i can not define it as the primary key because i want to have several payments under one company.
I have built tables in MS Access for a very simple shopping cart.
It includes: Catergories, Item, Customer, Shipment types, and Basket (cart). (for site) There is also a User table, but that is only used for the Backened side for login.
This is what I want the User in the backened to do: -add/edit/del Categories. -add/edit/del Shipment type. -add/edit/del Items according to Categories. (in one category, it can have many items, yet one item can go into more than one category).
This is what I want a browserer to do he/she enters the Site: -he/she can add item(s)(which are under categories) to cart[basket](no login needed). -At the basket(1 page), the user can view all the item(s) it chose, be able to change quantity, display subtotal, VAT, Shipment price, total price and enter it's customer details (e.g. name, mobile, etc), then sends form. These details are then "added" to the database and sends notification to admin(user) email. Therefore, no payment via client/server.
My problem is the relationships in Access. Do I need to connect all tables? I tried connected my ItemID table to the BasketID table and it got a bit confusing from there. Also the Categories got all messed up on me.
See my screenshots: http://salis.aspfreeserver.com/sample/relationships.gif http://salis.aspfreeserver.com/sample/table_menu.gif
Im building a web application to control the stock of a small company.
I already made the database with all the items and descriptions in one table.
This table would be the main warehouse. so now i have to create 3 different departments in different tables where i have to move stuff from the main warehouse. So if i have 10 boxes in main, and i move 4 to department1. then i would get 6 boxes in main and 4 in dep1.
My web app would be constantly updating the stuff being sold in departments 1,2,3.... so here is my question.
how do i make the database to update the quantities by itself in the main warehouse table if department X sells anything...?
Like in Excel, when you work with different sheets that can update the other ones if you change a number. the rows can be linked... can i do this in access?
Otherwise i would have to write a lot of extra SQL code in the ASP scripts and im really out of shape cause i don't program in ASP sisnce very long time ago... years.
Hello everyone. I'm not sure should I post this question here or at SQL Forum? However, my problem is this. I have 2 Tables, Table1 has AutoNumber as primary key, my second Table2 has index key field. I have one-to-many relationship on those two fields, with enforce referential integrity, cascade update and delete
The access program works fine until I upsized to SQL server. [u]Then I was unable to perform cascade update or delete. I have check SQL table (which I have little knowledge about) and seems to be okay, the relationship exists. But at ms-access I was unable to make the cascade update and delete.
i have imported 5 excel files and linked to a query named stock.i have linked the code,description and quantity from each table (ms excel imported) to stock. the problem is not all the codes available in the tables r available in stock.there r more than 25000 codes but only 2614 r listed in stock.i dunno whether its the problem with the way i have linked.so if u can help me with the problem i will be really thankful. 5 excel sheets contains the code,description and qty of 5 shops.in the query stock contains 1st column-id,2nd column-code,3rd column-description and 4th,5th,6th,7th,8th column the qty from 5 excel sheets respectively.
Field 1: Group Text field ( Primary key) Field 2: Group Description Text field Field 3: Uidgroup( Autonumber)
Table 2: Subgroup Text field
Field 1: Group ( I want to bound this column to Table 1's Group field that is column 1) I have set bound column property to 1 and column count 1 and the Subgroup table is showing group fields as input perfectly no issues in that )
Field 2: Subgroup, Text field( Primary Key )
Field 3: uidsubg( Autonumber)
Table 3: Email
Field 1: Group ( Bound to Table1's Group ; showing values in combo box, setted bound column property to 1 and showing group field perfectly, no issue in that )
Field 2: Subgroup (I want to bound Table2's subgroup field, which is column number 2, so I wrote 2 in bound column property and row source is table subgroup ; Here is some error comes up, values from subgroup field of subgroup table not being shown up in Email Table's subgroup field as combo box. )
Field 3: Email Text field
Field 4: uideml (Autonumber ) primary key
I want to prepare a Data entry form should have all these fields from all the tables. That should work in following way, first user selects Group then User selects Sub Group and write Email and save the record.
What relationship should I set, or shall I change the table structure.
I have upsized from Access 2003 to SQL Server 2008 R2 using upsizing wizard. Everything works fine. But I don't see relationship in SQL SErver 2008 R2 if I go to database. But I set relationship in Access 2003 before upsizing it.
Amso I don't see relationship (diagram) in Access 2003 , which I was able to see before.
So do I need to again recreate the relationship amongst the table in Access 2003 Or SQL SErver 2008 R2 ? I thought, if you link tables, everything should be taken care but i don't see relationship structure any more.
I'v looking for since a couple months a go to make a report direct from access form using crystal report but i havent found it yet. I'v tried this code and its giving me errors. " run time error 1004 method range of object _global failed "
how to make a report using crystal report direct from ms access as front end application ? is it possible to use crystal report ?btw i use database sql server 2008 and MS Access 2007 as my frontend application.here's the code that i'v found and gives me an error
Dim CR As New CRAXDRT.Application Dim rep As CRAXDRT.Report Set rep = CR.OpenReport(Range(" ??? ")) * i getting error in this line, what should i do to fill it ?? rep.ParameterFields(1).AddCurrentValue "Boston" rep.ParameterFields(2).AddCurrentValue "Cars" rep.Database.Tables(1).SetLogOnInfo "tool", "db_tsel" rep.ReadRecords rep.PrintOut promptUser:=False, numberOfCopy:=1 ' promptUser:=True doesn't work
Hello all, I am trying to accomplish the following; I have a MS Access Form ( MS Access 2002) which has a botton used to send an email based on the current record on the form and I want to do two things at the same time if possible,
I want that when the user click on the "SEND EMAIL" botton, the system generates the email with the report as a PDF format. Right now I can generate the email using the following code;
Private Sub Email_Report_of_Current_Record__s__Click() On Error GoTo Err_Email_Report_of_Current_Record__s__Click
Dim strReportName As String Dim strCriteria As String Dim stDocName As String
[B]1.) send the report as a pdf file 2.) include another attachment within the same code so that everything happens at once. (like an instruction's document)
I know how to print to a PDF printer and how to attach a file in an email, but the purpose of automating is to have as few keystroke as possible and program the system to do what you what to do specially when you need to repeat the process over 100 times.
ANY ONE CARE TO ADVENTURE ON AN ANSWER?
P.S. I have seen some suggestions, but some do not work with Access 2002, others want you to buy Acrobat Wrtier and some have to be done through a module rather than embeding the code within a FORM botton.
My email is Chiqolate@hotmail.com if you care to discuss any further. :D
I was wondering if anyone might be able to help me here. In the report, when there is no data, the "error" comes out on every fields. My question is
For example in a table called student studID FirstName LastName Address Course 1000 Michael Fox 34 Phillips st Science 1001 1002 Huge Gret 23 Pounted rd Science 1003 Benjamin Meyer 1 Canterbury rd Science
Assuming there is a form that has a listbox that shows a list of students information and a button for viewing their details. When user wants to see studID 1000, all he does is select the studID 1000 and click button "view". A report shows up with all the details of the 1000 student ID. It has no problem printing the result with studID: 1002 and 1003. But when he want to see the studID 1001, in the report,
My question is if it's possible for me in the report to be able to know the linkcriteria so that i know that the user selected studID 1001. Because i have tried to figure out what the selected studID was but all i got is the "error" eventhough there is a value for studID in the case of 1001. Is this possible or something that is beyond our control? All I need to be able to work out is what studID is selected in the event of nodata.
I have a specific requirement wherein one of the columns of the report should retain its previous value based on some creteria. so please tell me what should be the control source for this column. In other words, how can I access the previous record of a particular column.
I am trying to build a database for my share trading and am having trouble doing some calculations in a Report.
In the Detail section of the Report I do this calculation: =IIf([LVR]="0","0",((([Units]-([Margin Amount]/[Unit Price (o)]))*[Close Price (c)])/(1-[LVR]))+(([Margin Amount]/[Unit Price (o)])*([Close Price (c)]-[Unit Price (o)])/(1-[LVR])))
This calc is done on all my open trades and it returns the answer i want which looks like:
Margin Calculations
Open Trades
ASXCodeCurrent ValueMargin Available
FLT$5,500.00$18,333.33
SGN$3,700.00$3,200.00
AXN$13,200.00$33,000.00
CPU$9,250.00$17,821.67
AUN$15,600.000
Total Margin Available:$5.00
Inthe report footer i attempt to sum the answers this calculation returns using the Expresion: =Sum([Margin]) to get Total Margin Available.
Margin is the name of the field that contains the above calculation and when I try to veiw the report it prompts me to enter a parameter for Margin? I would have thought no parameter is required as all I am asking it to do is sum the numbers the calculation is churning out, however if i enter a value ie 1 Total Margin Available totals the number of records and multiplies it by the value i entered as you can see in the above example ($5.00).
I'm a little flummoxed by this... I have a report where I need to add up people's time according to the "class" of time. Currently, I have one field for "Hours" (Numeric) and one field for "Class" (also numeric).
I am able to get a grand total of "hours" - but I need to be able to do a kind of "if, then" addition.
For all records on that page, I need to do: Class 1 - Total hours Class 2 - Total hours Class 3 - Total hours -------------------- Grand Total hours.
I can't find anything useful in the online help - other than to get the "Grand total." Does anyone have any suggestions for me, or perhaps a sample they've done with a similar function?
I do a report in ACCESS 2000. In the footer of the report I have 3 fields and the number that appears in each one is calculated in the code of the report. When I preview the report all the numbers are correct, but when I print the report all the numbers in those fields are doubled. I have put the code in both Format and Print, but still the same problem. I would appreciate any hints to solve the problem. Rgds
I send a report in an email in rich text format and it works. But when you open the report there is no image, lines or rectangles. The format is ok but image on top of the report and lines are missing which I used for column captions. If anyone know how to handle this problem, I will really appreciate.
The query shows the total rent collected per property and the total expense per property. It then has a sum in it to work out the total profit - SUM(rent collected - expense paid.).
This works fine and the report works fine. But i want to add the totals in the bottom of the report.
So i have three unbound text boxes, one for each column. And the control source is
Sum([rentcollected]) Sum([expensepaid])
these two work fine, but then i have tried to work out the total profit but can't get this working. If i do Sum([profi]) this doesn't generate the real profit as it just totals the profit column which might have negatives.
If i put in sum([txtTotalRentCollected]-[txtTotalExpensePaid]) then when running the report it prompts for the values of the text boxes - these are the names of the text boxes the totals are calculated in.
