Hi All.
I have a database with 2 fields
1 with a date of birth
2 with the date 14 days after the date of birth.
is there a way of putting in say 14/08/1980 into field 1
and automatically putting 28/08/1980 into feild 2.
would appreciate any assistance anyone could give
Hi,
I am fairly new to access and still am not sure I fully understand how to do what I would like.
I have a fairly large database for community participants... On the "master database" if you will, I am entering either the child or parent who may participate in the programs - with their demographic information.... (some participate in various programs, or only one)
On master table, each participant is given an autonumber and identified by which programs he or she may be participating in.
On a sub tables, I would like to take attendance for each program.
Is there a way, I can create a table for one program and for access to identify all particpants from a particular program, pull in participant name and allow for entry.
1. Is this possible?
2. IF so, will this create a problem if the participant is in multiple programs?
3. I am trying to minimize the reentry of participants, and if new members are added at a later date, that access automatically places these names on the sub tables of attendance///
Thank you for any and all help you are able to provide....
M
I have a database that includes dates in a table, and a form that I use to show items that are issued from a supply crib. The date is automatically input by the database. I have designed a report with all the data that I need. My problem is that I need to be able to print this report according to a specific start date and end date (which is usually a 7-day period), and I can't seem to get this accomplished. Could someone please guide me in the direction that I need to go? I have a query designed, but I don't know how to apply the query to my report. For example, upon opening my report, I need it to ask me for a start date and an end date, and print the report according to the dates that I input. At the present time, the report won't ask me for these dates. Any help would be greatly appreciated.
View 1 Replies View RelatedI would like to create an Access Database that calculate due date based on 2 tables, one with invoices and supplier details. The other one with Supplier name et payment terms.
Input:
Table with the following details:
- Supplier Name
- Invoice Date
- Currency
- Amount
Output:
Table same as input but with a column that shows for each record the Due Date. The due date would be calculated by adding payment terms in days from another table to the Invoice date.
Hi all,
I am new here, I am never one to ask for help, but I am desperate to get some assistance and hoping someone may be able to help me. I have just basic knowledge of MS Access, but no where near the skills needed to acheive what I am needing.
To explain, I am involved with a national memorial of all the Truck drivers who have been killed on our roads. I have a database in access, (near 1000 individuals and sadly growing) but wanting to create it into a form style. I also need to add a photograph of each deceased person and a second photograph of the plaque which they are on. Years ago I saw a form which I was impressed with, it looked so professional but never had the oportunity to investigate or disect it :)
I am hoping sks who will enjoy the challenge may be able to assist me.
more about the memorial can be seen at www.tarcuttamemorial.com
Thanks Dianne
Hi all,
please note that I am trying to create a database regarding the inbound shipment management of a warehouse.I have the following tables that I need to link:
SUPPLIERS
ORDERS
PRODUCTS
INVOICES
CASES
Every supplier has many orders.One order has one or many Products.So far is easy beacause I link them with one to many relationship.The problem is that one case has one or many invoices and each invoice has one or many orders.
So there is a conflict because the primary key of orders should be linked twice.
Any suggestions?
please help.
thanx
I need some assistance with how to use the Nz function. If you could please help me rewrite part of my SQL (shown below), I would appreciate it. I am a newbie and don't understand some of the programming syntax.
Background
-----------
I have two columns, RevisedRequirement and TotalFCbyS in which I need to do some calculations and insert two new columns named Variance and VariancePct.
Issue
-----
Both the RevisedRequirement and the TotalFCbyS fields can have nulls. I need to convert any instance of a null to a 0 so that I can perform the calculations and not get any error messages.
Current SQL Excerpt
-------------------
This creates the Variance column:
IIf(IsNull([qryProgramFundingAllocation].[RevisedRequirement])=True Or [qryProgramFundingAllocation].[RevisedRequirement]=0,0,([qryOpPlanForecast].[TotalFCbyS]-[qryProgramFundingAllocation].[RevisedRequirement])) AS Variance
This creates the VariancePct column:
IIf(IsNull([qryProgramFundingAllocation].[RevisedRequirement])=True Or [qryProgramFundingAllocation].[RevisedRequirement]=0,0,([Variance]/[qryProgramFundingAllocation].[RevisedRequirement])) AS VariancePct
The SQL shown, if I understand correctly, treats the Null as if it were a zero, but does not replace the null with a zero. Again, please help me with how to re-write the SQL so that it converts the null to zero.
I have two tables in my database. The first is a list of email addresses and the second is a much smaller list of email addresses I would like to remove from the first table. Is there a query or SQL statement that I can use to accomplish this task. Thank you from grateful Kenny.
View 2 Replies View RelatedFirst time poster, so I hope someone shows me the light and makes your forums shine! :)
I have a POS system that was designed in house which uses Access. I am trying to create some queries outside of the system to retrieve some data that has been being stored. I am basically wanting to know which items have been in stock for over 6 months and have never sold.
I have a query designed to show me this data for one store at a time, but nothing that shows me if an item is so poor it has never sold at any stores. This is my single store query:
SELECT Inventory_ProductTable.Artist, Inventory_ProductTable.Title, Inventory_ProductTable.Quantity, Inventory_ProductTable.Category2
FROM Inventory_ProductTable
WHERE (((Inventory_ProductTable.Quantity)>0) AND ((Inventory_ProductTable.LastBuyDate)<Date()-"180") AND ((Inventory_ProductTable.masterStoreID)="CDT001") AND ((Inventory_ProductTable.DepartmentID)=1) AND ((Inventory_ProductTable.LastSaleDate)=""));
It is pulling the artist, title and category of the item out of the database to display the informtion that I am needing (for the most part). Quantity of 0 and last sale date "" basically means there is no record of it being sold, so this gives me my zero quantity sold criteria for anthing that has been in stock for 6 months or more (the last buy date criteria).
I am not sure what other info I need to provide, but I would be happy to do so. I am a n00b at this and really love to learn, so lay it on me!
thanks in advance.
I created a form using a text boxes by using the field list icon and dragging the field I wanted to make into the text box onto the form. In the properties of that text box, under the format tab I set the format for "short date". Under the data tab in the properties of the text box I put in the control source this forumla =DateSerial(Year([Date of Service]),Month([Date of Service])+10,Day([Date of Service])). "Date of Service" on my form is the name of the source field it calculates from. What the forumla does is calculates from the date entered into my Service Date field and whatever date is in that field it adds 10 months to it. This field with this formula in it caluclates the date of my contacting the customer to setup an annual service for them. I also have another field that calculates the annual service date using the same formula only changing the +10 to +12.
When the Date of Service it entered the Contact Date field automatically adds 10 months to the date entered into the Date of Service field. Same of the Date of Next Service field only instead of 10 months it adds 12. So my formulas are correct. However when I go to my table I can not see any of the caluculated dates entered into the table. I can see it in the datasheet view of my form. My question is how do I create a query to pull only contacts dates from this form for a specific time frame? When I attempt to create a query I can only see table fields which do not contain the formulas for calculating the dates. Or can the fields that calculate the dates on my form be linked so the calculated dates are linked into the table and updated on the table when I update the form?
Keep in mind I am new to access so please give very detailed and specific instructions on a solution for this issue.
I have 1 table with three columns of data (town, county, and zip). After entering the data on my form from a dropdown down box (town), which is represented by the first column of data in my table, I want Access to automatically populate the second and third boxes in my form that are representive of the second and third columns in the table, county and zip respectively.
View 2 Replies View RelatedI generally use a bunch of these within an event procedure on my Opening Form to ensure that certain options are set
Standard set
Application.SetOption "Confirm Record Changes", False
Application.SetOption "Confirm Document Deletions", False
Application.SetOption "Confirm Action Queries", False
Application.SetOption "Show Hidden Objects", False
Application.SetOption "Default Open Mode for Databases", 0
Fine for 97
In later versions I want to also swith off Windows in Taskbar
The Help in later versions offers no help at all
What I am looking for is I think something like
Application.SetOption "Windows in Taskbar", False
This is not recognised. Tried a few variants and also searched forum but nothing yet
Anybody with a solution please
Len B
Hello
I have a database (Access 2007) that has two tables, reports, queries and so far all seems to be working and going well. Quite impressed with it to be honest for my lack of skills.
However, I've run into a bit of trouble and need assistance getting a combo box to work with another combo box to produce list kind of thing.
The database is a vehicle database and I need the 'Vehicle' combo box to give me a list of vehicles. I then need the 'Model' combo box to produce the models of the car selected in the 'Vehicle' combo box.
Example - If I select 'Ford' from the 'Vehicle' combo box list I want all the models to be displayed in a list form thing in the 'Model' combo box so the user can easily select make and model of the car.
I have two tables:
CarMM
VD
The 'CarMM' table contains a list of around 1,500 records of car makes and models. First column has the 'Vehicle' make and the second field coloumn has the 'Model'.
The 'VD' table contains all other information like the charges, name, the make and model of car once selected in combo box.
I then have the form called 'VD'. In this form contains the combo boxes I want to be cascading.
Any advice on how to do this? I'm quite new to this so any help would be nice. Thanks you very much.
Hello
I have a database (Access 2007) that has two tables, reports, queries and so far all seems to be working and going well. Quite impressed with it to be honest for my lack of skills.
However, I've run into a bit of trouble and need assistance getting a combo box to work with another combo box to produce list kind of thing.
The database is a vehicle database and I need the 'Vehicle' combo box to give me a list of vehicles. I then need the 'Model' combo box to produce the models of the car selected in the 'Vehicle' combo box.
Example - If I select 'Ford' from the 'Vehicle' combo box list I want all the models to be displayed in a list form thing in the 'Model' combo box so the user can easily select make and model of the car.
I have two tables:
CarMM
VD
The 'CarMM' table contains a list of around 1,500 records of car makes and models. First column has the 'Vehicle' make and the second field coloumn has the 'Model'.
The 'VD' table contains all other information like the charges, name, the make and model of car once selected in combo box.
I then have the form called 'VD'. In this form contains the combo boxes I want to be cascading.
Any advice on how to do this? I'm quite new to this so any help would be nice. Thanks you very much.
I have DB with a Switchboard that has a button for Bin Number (Part Number). It is based on a parameter query that asks you to input your Bin Number. Once you enter the Bin Number it pulls all information for that Bin Number from a table that contains the Bin Number, description, Manufacturer P/N and Manufacturer and shows this in the query.
What I need to do is have this information each time a different Bin Number is entered to be stored in specific table (datasheet style) for printing out just a list of specific Bin Numbers and their information. As the parameter query stands now, each time I input a different Bin Number it clears the previous information. Can I tie in a make table query in some manner that will store the selected Bin Number info?
Can someone make suggestions on the easiest and quickest way to set this up? Keep in mind I am still learning Access so please be detailed in your comments. Thanks in advance for any assistance.
I have created a query based on two akwardly designed tables that ultimately relate a member of Congress to a specific Committee and Subcommittee. Unfortunately I cannot alter the tables, as I receive weekly updates from an external source.
This being the case, once I have created the query, a "-" appears in the Subcommittee column, where the Member of Congress serves on the Committee, but no Subcommittee or the Committee does not have a Subcommittee. Although I could exclude the "-", however when I do this certain committees are removed from the final results. So I attempted creating an expression, where "<Not Assigned to a SubCommittee>" appears if the Committee does not have a SubCommittee. Unfortunately there are a few committees that fall under this. I created the following expression:
=IIf([HMC_Name]=("Committee on Budget""Committee on Standards of Official Conduct""Committee on House Administration"),"<No Subcommittee Assignment>",[HSC_Name])
Yet this expression returns #Error. Any suggestions?
Hello, My first attempt at a Crosstab query and I need some assistance with the output. The expression I use is below and it returns the correct infornmation by month, but i need the year to be included as well as the data goes back several years.
I would like it to calculate so that i get Jun-03, July-03 etc but i only get the option to choose one field type ie Year Month etc
Any assistance greatly appreciated
Format([APM Outcome Effect dt],"mmm")
Hi, I would like to have a toggle button on my form that when clicked it changes the button colour and the button text. The toggle button doesnt have to be bound.
cheers
I know to most this is simple, but as a newer user putting everything in its right place is hard. Have checked multiple posts for my situation and cannot locate. I know I need to use DLookup.
Membership table used to built form has among others, a contract #1 field (9-digit Soc.Sec. #) and a Contract #2 field with another randomly assigned 9 digit number. Current form set up to accept contract#1 in an unbound text box which then autopopulates other fields pertnent to this member. Member should be giving us Contract #2 but that will not always happen. Want a DLookup so that if the #1 num is put in it will automatically input the correct #2 number.
Not sure where to put it and exactly how to format. Can someone please help me. Both fields on the same table.
I have been unsuccessful in finding the correct formula for the APR of a loan to calculate automatically. Does anyone have the formula for calculating APR?
View 1 Replies View RelatedI am trying to open a form to a specific record but when I run the code I keep getting a syntax error (missing operator) I indicated it in bold where the debugger takes me. Could anyone assist me. Here is the code: This code appends the current record to a new table then deletes it from the old. Then i want it to open the new form at the current record I just appened.
Private Sub Repair_Click()
DoCmd.OpenQuery "appendtorepair", acViewNormal
DoCmd.OpenQuery "DeletefromInv", acViewNormal
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Repair"
stLinkCriteria = "[PAM_ID]=" & Me![PAM_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
End Sub
I am doing an assignment where i am required to
Quote: "Find the total price (SUM) of all stock items in the database (use total query and find the SUM of the [price of stock]*[quantity on hand]"
Ive tried several times to do this, each time unsucessfully because im unsure how to go about it. i can get the sum of those two things, but i cant multiply the two.
Thanks in advance for the help
I'm bringing an archived db into Access, with entry forms to continue on into the future for maintenance. In the past, some dates were recorded only to year (yyyy). Obviously, I won't continue that, but I need to integrate the old and new in a fashion that will sort. For instance, one form has a subform section based on a report to show the old entries, while below are unbounded text fields for making new entries (with a Save command button).
How to integrate partial old text dates with newly recorded dates.
I have the attached database which calculate the Difference between dates.I have also two problems like i shown in the image
Code:
Option Compare Database
Option Explicit
Sub Recalculate()
Dim rst As DAO.Recordset
Dim dff As Long
[code]...
I manage academic papers whom are written by multiple authors and reviewed by multiple reviewers. Currently, all the data is stored in one big table and I'm not able to get any statistics or real tracking out of it.
I have set up a test DB with 3 tables so far:
Main
Table_ID -auto sequential key
CORP_ID -internal paper id
STATUS -open, overdue, accepted, rejected
REC_DATE -received date
MOD_DATE -record last modified date
TITLE
AUTH1_ID -linked to Author.ID
AUTH2_ID -linked to Author.ID
REVR1_ID -linked to Reviewer.ID
REVR2_ID -linked to Reviewer.ID
Author
ID -key field
NAME
DESIG -designation
EMAIL
PHONE
Reviewer
ID -key field
NAME
DESIG
EMAIL
PHONE
RATING
My goals are these:
Create a module that manages the authors. They are usually the same group of people. Each author should only appear once.
Create a module that manages the reviewers. Also, usually the same people (but different than authors). Each reviewer should only appear once.
Create a main form that allows the end user to add a record (paper), assign author(s), assign reviewer(s).
Create Query/Report that would then be able to:
- track authors and their papers
- track reviewers and their reviews
- as well as track papers and their authors/reviewers
When I create subforms, they appear to be 'backwards' meaning that the Author form tends to be the main form and the Main form tends to be the subform. I think this is b/c the Author.ID field is the key field, whereas the Main.TABLE_ID is the key field, but not relevant for other than keeping things straight, nor is it related.
Can someone point me in the right direction?
Alternatively, has someone created a database like this already that I can buy? :)
Thanks!
I have a query that is based off of a union query
SELECT clvpertech.clvtech1 AS Technician, Sum(clvpertech.CLVCode) AS CLVs
FROM clvpertech
GROUP BY clvpertech.clvtech1;
This is returning a sum of all the procedures a technician does for the entire database.
This is working.
I need to break this down by day, week , month, and year base off of the datecomp1 or datecomp2 or datecomp3 or datecomp4 fields having a date in them.
I made this query to get the daily results and it works
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=Date$())) OR (((clvpertech2.datecomp2)=Date$())) OR (((clvpertech2.datecomp3)=Date$())) OR (((clvpertech2.datecomp4)=Date$()))
GROUP BY clvpertech2.clvtech1;
How can I add different queries to get the results by breaking down the date field for year, week, and month?
I tried this for the month but I get Invalid Procedure Call when I try to run it???
SELECT clvpertech2.clvtech1 AS Technician, Sum(clvpertech2.CLVCode) AS CLVs
FROM clvpertech2
WHERE (((clvpertech2.datecomp1)=DatePart("mmm",[datecomp1]))) OR (((clvpertech2.datecomp2)=DatePart("mmm",[datecomp2]))) OR (((clvpertech2.datecomp3)=DatePart("mmm",[datecomp3]))) OR (((clvpertech2.datecomp4)=DatePart("mmm",[datecomp4])))
GROUP BY clvpertech2.clvtech1;