Retrieving Data From Unlinked Tables
Dec 13, 2007
I have a table (Working_Year) that is storing a single field which is the year (current_year) that is selected by the user in my initial form when the database is loaded. Am I able to use that data in other forms and reports with out linking the table? I would like to simply display the current_year the user is working in a textbox somewhere on the forms (example form named: "Main Entry") and use the current_year as a filter on reports (example report called "Gifts - by Type").
View Replies
ADVERTISEMENT
Aug 14, 2015
I have a distributor table within an existing database that I use to keep track of quotations to each distributor for each project our distributors bid. It was simple to design 5 years ago when we only had to quote one mfg to each distributor. However, I now have to quote distributors multiple manufacturers and tried to develop a second "estimate" table to handle things. The issue arises when I want to view all the quotes I have for one distributor, no matter which manufacturer is involved.
I have set up a relationship between the [Distributors] table and [Special_lite Dist Quotes] and the [Distributors] table and [tbl-Unassoc-Quotes] as shown in attached (.jpg)
My intents is to go into the Distributor main form and click on an estimate button that will open a form to show all the estimates I have given a particular distributor. I have been able to do that when I only use the (1) [Special_lite Dist Quotes] table. However, things get pretty screwed up if I try to add the second table [[tbl-Unassoc-Quotes] to the underlying query.
View 4 Replies
View Related
Dec 28, 2006
I have four tables....that do no require relationships.
The four tables have different fields, but have one field in common, the Name.
I want to create a query with all the Names from the tables.
How?!
View 1 Replies
View Related
Mar 26, 2015
I have three tables.
The first is my table of pupils which is linked to the second table: a list of awards and a score 1-5
My third table is the list of 10 awards.
My problem is that (in my report) I want to show all 10 awards for each pupil regardless of if they have achieved them. I'm struggling with the underlying query to always show all 10 awards.
So, little Johnny has achieved a score of 5 in two of the awards, however the report card will show blanks for the remaining awards on his report.
View 4 Replies
View Related
Nov 13, 2007
Hi,
I am new to access and need help trying to retreive data. Basically I have a unique ID field (123545). I what a user via a form to be able to retreive data using this unqiue ID. Does anyone know the best way to achieve this in access please?
Thanks
Ket
View 1 Replies
View Related
Feb 14, 2005
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below (although the table headers have moved a bit). There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litreUp to 1000cc1001 to 1500cc
75.08.5259.653
75.28.5509.682
75.58.5759.710
75.78.6009.738
75.98.6259.767
76.19.6509.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
View 5 Replies
View Related
Dec 15, 2005
Does anyone know how to setup an ODBC to the Ebay Web Server?
View 3 Replies
View Related
Feb 16, 2005
I put this on the tables forum but my answers have now stopped, can anyone here help me with how I get this information to appear on a form....
I have created an expense database but I now want to try to add fields to the main form which will allow the users to select their car engine capacity and the price they paid per litre to establish how much VAT can be reclaimed. A small extract from the table from customs & excise is set out as below. There are 5 engine capacity headers and numerous pence per litre rows.
Pence per litre ....Up to 1000cc.......1001 to 1500cc
75.0 ..................8.525.................9.653
75.2 ..................8.550.................9.682
75.5...................8.575.................9.710
75.7...................8.600.................9.738
75.9...................8.625.................9.767
76.1...................9.650.................9.795
So if someone had a car with the engine size between 1001 to 1500cc and had paid 75.7p per litre for their fuel we could reclaim VAT @ 9.738p per mile.
Is there anyway I can get access to look up this information for me?
.................................................. .................................................
Ans:
It would be nice if you could find out the formulae for working out the pence per mile bit based on the cc.
Either way, I think your table needs changing to this design
Price. . . . CC. . . . . . . . . . . . . VAT
75.0 . . . .1000. . . . . . . . . . . .8.525
75.0 . . . .1000 to 1500. . . . . . .9.653
etc
etc
Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.
.................................................. .................................................
Thanks,
Unfortunately I can't get hold of a formula. I'm still not sure how I would look up a value, even if I changed the table as you suggested. The user would need to select a cc size and then a price per litre which would then need to be cross referenced to give a value. I could set up different tables for each engine size, but then I'm not sure how I could point the answer at the correct table.
I don't even know if what I am trying to do is possible in access.
.................................................. .................................................. .
Sharon, you could have the price per litre in one ComboBox and the engine size in another. Both these would then be the criteria for a query.
Can you post your Db and I'll have a look for you, failing that I could knock one up (in Access97)
Col
__________________
This ain't no technological breakdown. . .oh no, this is the road to hell.
.................................................. ..................................................
I think I've attached the file, but I've never done this before so it might not be there!
The table I'm trying to create is called pence per litre but it is completely stand alone at the moment until I can work out how to get any information out of it. I have changed the table to your suggested layout but have only entered a few records, there are hundreds to be entered if it can be made to work!
Thanks
Sharon
.................................................. ................................................
I can't seem to get the file to attach it doesn't seem the right extention code it's an mdb
View 4 Replies
View Related
Nov 18, 2005
In my employee attendance database each record contains an employee id#, a number corresponding to an attendance infraction, and a corresponding date. Each week a clerk queries the database to pull up records for all employees who have a yearly 'total number' of infraction values over a certain numerical limit.
Any suggestions as to what is the best way to:
1 - Calculate that yearly 'total number' for every employee.
2 - Retrieve the date of the most recent attendance infraction for each employee that has a total value that is over the limit?
Thanks
View 1 Replies
View Related
Jan 18, 2008
Hi All,
I need to make a payment based on the latest Verify Number to a specific person so i am trying to create a form that is focused around a person, looks up the latest Verify number and can make a new payment number to add new payments.
In my tables, it works perfectly excluding the latest Verify number whereas i do not have any filters set. The Verify numbers can change which is why i need to make a new payment based on the latest Verify number. Using this number, i can add many payments to a Payment Number and add many Payment Numbers to a Verify Number for example:
John
Verify NumberGo4566546
Payment Number 44
Payments101Work carried out £800
102Delivery costs £100
103Material Cost £400
Payment Number 49
Payment 168 Work Carried out £700
170Work Carried out£450
Verify Number Go4566952
Payment Number 50
Payments171Work Carried Out£900
177Work Carried Out£500
Steve
Verify Number Go5877654
Payment Number 51
Payments178Cleaning£120
My Tables are linked as follows:
Person Table( name of table )
PersonID( unique ID of that person )
Verify Table( name of table )
VerifyID( unique ID of the Verify Number )
PersonID( linking to Person Table )
PayNo Table( name of table )
PayNoID( unique Payment Number )
VerifyID( linking to Verify Table )
Payments( name of table )
PaymentID( unique Payment ID )
PayNoID( linking to PayNo table )
The Payments figures have no relevance as they are numbers given by the person to me so i do not need to link them, i only need to link the table they are entered onto.
I am trying to get this onto one form whereas i can see who i am paying, the latest verify number, the last payment number to the person and the last payments in a table. Then, i can click add a new payment number, and i can add a new set of payments t the newly created payment number.
Hope this makes sense!!
many thanks,
Nigel
View 1 Replies
View Related
Aug 25, 2015
i have the below code which inserts data and then i want to retrieve the value in the first two columns but firstly it says "you must use dbseechanges" but i have put this into the code you may see below,
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
dim sSQL as string
[code]....
View 10 Replies
View Related
Aug 2, 2006
I have a database with a series of fields, all set to a String datatype, that I want to be able to fill and then use with MS Word as a mail merge data source. This function lets you query a MS Access db and use the results to fill in predefined spots attached to the various data values in the table. This is great for my needs however a large percentage of times that I have a dash in the value being queried it does not return any results. For example, 01-01 would return no entry found even though I can clearly see it in the database, however it would work fine if I queried for 0101 or any other value in the given field.
Basically I'm trying to figure out why dashes would cause problems. They are obviously fine for being stored in the access database and don't cause any troubles until they are queried for, also retrieving data containing dashes is fine so long as that value is not the one being queried for.
I basically can't avoid the dashes in the data for this setup and as such hopefully can figure out why this happens and how to get around it. Thanks for any help offered.
View 1 Replies
View Related
Aug 3, 2005
Hopefully someone can help me this (and hopefully the solution isn't staring me in the face)
I am trying to export data from a cell in a worksheet to a field in an Access database that I am building. The database has two tables, tblCompanies and tblQuotes. It works through a macro accessed by a command button, which should export the total of a quote into the field Quote_Price in the tblQuotes table, which the users will access through a form. The problem is that if I export the data from Excel, I get an error message saying, "You cannot add or change a record because a related record is required in table 'tblCompanies'. The code looks like this:
Sub NewQuote()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:Documents and SettingsDJEdwardsMy DocumentsTest Foldermarketing.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "tblQuotes", cn, adOpenKeyset, adLockOptimistic, adCmdTable
r = 55 ' the start row in the worksheet
Do While Len(Range("I" & r).Formula) > 0
With rs
.AddNew
.Fields("Quote_Price") = Range("I" & r).Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
I can import the cell contents from Access with no problems, but I would like the user to be able to do it from Excel to simplify things.
I'd be grateful for any help!
Cheers
Dave, England
View 2 Replies
View Related
Nov 15, 2014
I have quite an extensive form linked to a table. When I add new columns to the table I seem to have a problem getting the form to read the data.I have just added a numerical column to the table and added a text box on the form that is bound to it. When I try to pull up the value using VBA it is blank, even though an entry is visible on the form. I have set the text box to general number and the entry shows in the actual table. When typing in the entry into VBA it capitalises where required so it must be registering the table entry. However the value it pulls remains blank.
View 2 Replies
View Related
Oct 20, 2005
I am using a Table to store Parameter Values for a Split Multiuser DB. Question I have...Can I have this Table on the Front End while all the others are on th back end? The use for the table is to store parameter values for multiple queries without the user having to reenter the parameters.
I've tested this and it seems to work...but would like to know if there may be complications.
FYI. I am a Novice VBA user.
Thanks,
Felix
View 1 Replies
View Related
Aug 22, 2005
Hi, How don't know how stupid my question is but i was wondering if it was possible to insert a form inside an other form without linking them???
The reason for this is that the 'inserted' form would work as a 'search' form and could display usefull information for the main form to be filled even though there is not really any fields that can be linked together. Is anybody got suggestion for this matter? :confused:
View 5 Replies
View Related
Jun 4, 2013
I have an unlinked subform on a form. This unlinked subform contains the contents of table: employee. What I want to do is have a textbox that, when typed in, will auto filter out records that don't match what was typed so that once you have correctly typed the employee name all you are left with is records of employees with that name.
View 4 Replies
View Related
Oct 9, 2007
Dear friend,
I have a question if anyone can help me out. I have as assignment to do for a small firm. Dataentry application primarily with few search and edit functions. This will be used by multi-user.
I am yet to decide whether to make the form and the database linked or unlinked. What I mean is:
1) I want to keep form and db in seperate mdb.
2) I have to decide if the form (in seperate PCs) should be linked to the db over network or should it be programmatically connected.
3) I have a feeling that if the tables are linked with the form (the tables that i see in the database window), then when I open the form the underlying records/table will gush into the client PC jamming the network.
4) If i programmatically connect the form with the tables in the network db based on query then probably only the required record will come through the network and fill my form not jamming the network.
Any idea, pls help.
Regards,
Prodigy.
View 5 Replies
View Related
Nov 18, 2014
I have a back-end db in access 2010, linked to a set of sharepoint tables on office365.
I want to make a completely unlinked copy of the db to store a "snapshot" of the data on a fixed date.
I have tried "save as" using the "Save as Local Database" option, but for some reason, it still saves as linked tables.
How to go about it?
View 5 Replies
View Related
Nov 22, 2013
How to calculate all records that have not yet been linked to other records. For example: I have 1 Product, which is linked with multiple components which is subsequently linked to subcomponents. All these types can be linked to Alternate Parts.
In order to reduce redundant information I would like to show the unlinked parts to the user. Therefore I would like to make a query which shows all unlinked parts.
I have the following tables with subsequent PK Part Number field values:
tblProduct->ProductPN
tblComponent->ComponentPN
tblSubComponent->SubComponentPN
tblAltParts->AltPartPN
I am unable to find a appropriate query structure with criteria for this issue due to the many different fields required and the fact that if in one query all these tables are shown no data will be shown ...
View 5 Replies
View Related
Feb 5, 2008
I have an Access2003 database that contains a table. The table has 2 fields. One is a counter and the other stores a picture which is datatype ole object. I want to do a one time export of the pictures. I want to save the pictures as jpegs in a designated directory. I know very little about working with ole object datatypes. Can someone tell me the easiest way to do this?
View 1 Replies
View Related
Nov 5, 2006
Hello All,
Is there a way to retrieve the very last entry to a table (via a query) without passing a value to the query.
Lets say I have a table Pets
ID desc
==============
1 Dog
2 Cat
3 Lizard
For example lizard was added last, is there a way I could pull just this out using a query? (keep in mind that I don't know wahat the last entry is, so I cannot pass any kind of value to the query)
Cheers,
Aaron
View 2 Replies
View Related
Jan 17, 2006
Hello all,
I would like, from a form, to get the file size of a particular file.
I would really like something like: FileSize("C: estfile.exe")
I am sure I have had something like this before, but cannot for the life of me find the answer I originally got!
Thanks!
View 3 Replies
View Related
Dec 6, 2005
I have a table containing the following two fields, one with monthly dates (end of month plus year) and one with profits (per month). However, for some dates the records are missing. For example, for the 31-1-1994 there is no record (not in the date field, nor in the profits field).
How can i create a query that will only show me the records if 10 or more monthly subsequent profits are known, so meaning that in those 10 months no records are missing? So that only the timespans without the gaps (missing records) are shown.
So if the 31-1-1994 and the 30/6/1994 record are missing, then the 4 subsequent records in between those two dates should not be shown,, since the amount of records is not 10 or more. However, if the next missing date would be 30/6/1995, then all the 11 subsequent records between 30/6/1994 and 30/6/1995 should be shown. Since the number of records is bigger than the required 10.
Thanks for helping me out!
Luuk
View 4 Replies
View Related
Jul 10, 2006
Hi,
I making a query which creates a list of customers in a month. For the start and end dates of the month, they are retrieved from a table and put into 2 seperate subforms. The query isn't working through, so I was wondering if anyone would see where I'm doing wrong.
In the order_date field in the query, I have this as the criteria:
Between [Forms]![sub_fltStart]![DateList_Start] And [Forms]![sub_fltEnd]![DateList_End]
If you want any more clarification then just ask :)
Cheers,
Ben
EDIT: spelling/grammer
View 2 Replies
View Related
Feb 21, 2007
Need some help, please.
I'm writing a simple report that needs to show individuals and the number of times that each individual has been designated the author and/or owner of a document.
The two tables in the query (simplified) are: Person, with columns personID (PK) and personName; Document with columns docID (PK), authorID and ownerID.
Each report line needs to show one line per person, with the ID, name, count of authorID and ownerID (showing the number of times he/she was designated the author and/or owner of one or more documents).
For example:
ID ... Name ..........Author ... Owner
1 .... John Smith .... 0 ......... 3
2 .... Mary Smith .... 2 ......... 0
3 .... Peter Smith ... 1 ......... 2
I need to create a query to retrieve one row per person, then do some kind of subselect (?) to count the number of matches for Person.personID against Document.authorID and Document.ownerID.
I'm having all kinds of problems in what I thought would be a simple SQL statement. Can't find anything out there, so all suggestions are welcome.
Thank you.
View 2 Replies
View Related
