I Think This Is A Real Noobie Question But Can Someoen Explain?
Sep 16, 2005
Hi, i know i have done this before but its been so long and i know its real simple so here goes:
Say i have a record in a table and i want to add records to that record in another table does simply creating a relationship between the two tables make sure the information is relevant to that record and how do i add more than 1 tables information to a form so i could show the record from the first table and say all of that persons orders from another table?
Thanks for your assistance
Alex
View Replies
ADVERTISEMENT
Jan 29, 2007
Is there any clear cut way to directly access a cell in a table via code?
Ive looked as far as my eye can look to try and find something quick and easy that allows me to cherry pick data out of a table, but ive had no luck.
For instance, I have a table named tblSANConnections with a field in it named fldServername.
I want a command button to go into tblSANConnections and snag information from fldServername of the current record displayed on my form, and hell, I dont know put it into a variable.
Can someone toss out a few key codewords that I can lookup to accomplish what im after? thank you
View 2 Replies
View Related
Dec 1, 2007
Hello there,
I have been bashing my head off of my laptop trying to get a query to return records where a date field is between two dates.
1. The field has a datatype of date/time
2. There are only two records in the database where the field Client.Refdate of record one is equal to 05/12/2007 and record two is equal to 05/01/2008.
3. The SQL was like this:
SELECT Client.clientMarital, Client.clientFirstname, Client.clientMiddlename, Client.clientSurname, Client.clientGender, Client.clientAddress1, Client.clientPostcode, Client.clientRefdate
FROM Client
WHERE (((Client.clientRefdate)>(#04/12/2007#) And (Client.clientRefdate)<(#06/12/2007#)));
However when I ran the query no results are returned.
I searched the forums and found this post: http://www.access-programmers.co.uk/forums/showthread.php?t=139619&highlight=dates
So amended the sql like so but to no avail:
SELECT Client.clientMarital, Client.clientFirstname, Client.clientMiddlename, Client.clientSurname, Client.clientGender, Client.clientAddress1, Client.clientPostcode, Client.clientRefdate
FROM Client
WHERE (((Client.clientRefdate)>DateAdd("d",1,#04/12/2007#) And (Client.clientRefdate)<DateAdd("d",1,([Client].[clientRefdate])<#06/12/2007#)));
Can anyone point out my error, or any potential things I have missed...I am going bonkers!
tenderfoot
View 5 Replies
View Related
Dec 4, 2007
Hi Folks
I need some help with this issue at the office.
I use Access to lift data from our departmental mailbox and put it onto an Excel sheet, this process was created by someone who no longer works for us so we don't have any help from that source and our IT bods seem to be avoiding our queries. The problem I have is that due to a recent e-mail server switch, Access is looking for the older server address and obviously can't find it so I need to find the part of the script where Access is calling the e-mail server so I can update it with the new one.
So far I haven't had any luck in finding this info and as I have no experience in using Access other that other peoples completed modules I simply don't know where to start. Can anyone please give me some pointers with this?
:confused:
View 2 Replies
View Related
Aug 24, 2007
I am building a database that will tell the company what items to dispatch to the customer first. It’s a food company. So getting products out on time is important. I will try explain it. Say the food company makes a batch of 30 apple tarts. This collection of products has a set batch number with all the relevant information attached. For exampleBatch no 4457 - qty 30 - created 10/10/07 - use by 10/11/07 Batch no 4458 - qty 30 - created 11/10/07 - use by 11/11/07Now lets say customer (joe Bloggs) orders 35 apple tartsThe food company wants to take all the first batch (4457) and 5 from the second batch (4458) The will have an order sheet with the followingApple tart batch 4457 - 30Apple tart batch 4458 - 5 After the order is gathered it will be given an order number of say order 8897The company can now go back and look at order 8897 and see what was shipped in that order and what the batch numbers where for all products. Any help would be greatly appreciatedAlan
View 14 Replies
View Related
Apr 6, 2007
Can someone explain what the following code does.Thanks in advance
Set dbUser = DBEngine.Workspaces(0).Databases(0)
Set rsUser = dbUser.OpenRecordset("SELECT User.UserName FROM [User]WHERE (((User.UserName)='" + txtUserName + "'))")
View 1 Replies
View Related
Oct 17, 2006
Hi all,
I was recently asked to rewrite some stuff and i was wondering if the query below can be expressed with just ( SELECT, FROM, WHERE, GROUP BY ) Instead of JOIN.
Someone else wrote the query below and Joins always confuse me ...
Thanks in advance
SELECT Grpoffic.CODE, Evoter.precinct, Evoter.grp, IIf(evoter.style Is Not Null,evoter.style,evoter.[activator code]) AS ballot, Evoter.party, Evoter.[date voted] AS [voted date], Evoter.certnum AS [voter id], Evoter.name, Evoter.address, Evoter.[ev location] AS [Location voted], Vote_status.status, AB_CODE_reason.reason AS [absentee reason]
FROM Vote_status, AB_CODE_reason RIGHT JOIN (Evoter INNER JOIN Grpoffic ON (Evoter.precinct = Grpoffic.pct) AND (Evoter.grp = Grpoffic.grp)) ON AB_CODE_reason.code = Evoter.reason
ORDER BY Grpoffic.CODE, Evoter.precinct, Evoter.grp, Evoter.name;
View 1 Replies
View Related
Mar 6, 2008
Month Number: Mid([Date],4,2)
I also have the SQL that goes with the query:
SELECT Mid([Date],4,2) AS [Month Number], Count(tblOrders.Order_Number) AS CountOfOrders, Sum(tblBikes.Price) AS SumOfPrice, Format([Date],"mmmm") AS [Month Name]
FROM tblOrders INNER JOIN (tblBikes INNER JOIN [Order/Product] ON tblBikes.Model_Number = [Order/Product].Model_Number) ON tblOrders.Order_Number = [Order/Product].Order_Number
GROUP BY Mid([Date],4,2), Format([Date],"mmmm");
:confused:
View 6 Replies
View Related
Aug 11, 2005
Hi expert. could any one explain this code for me. I do not understand some parts of it. I know the output but not some parts of the code.Thanks
Code:Private Sub processButton_Click() Dim fso As New Scripting.FileSystemObject <------ Dim io As Scripting.TextStream <------ Dim db As DAO.Database Dim rst As DAO.Recordset Dim fld As DAO.Field '''Declreaing our variables Dim strBase As String Dim strInsert As String Dim strFields As String Dim strValues As String Dim strTemp As String Dim strFile As String Dim strName As String Set db = CurrentDb() Set rst = db.OpenRecordset(Me![ComboBox]) strBase = "INSERT INTO " & Me![ComboBox] & "({%1}) VALUES ({%2})" <------ strName = "c:" & Me!ComboBox & " Data.sql" With rst <------ While Not .EOF strValues = "" <------ If Len(strFields) = 0 Then For Each fld In .Fields If Len(strFields) > 0 Then strFields = strFields & "," & fld.Name & "" Else '''strFields = "[" & fld.Name & "]" strFields = "" & fld.Name & "" End If Next fld strInsert = Replace(strBase, "{%1}", strFields) End If For Each fld In .Fields If Len(strValues) > 0 Then strValues = strValues & "," End If If IsNull(fld.Value) Then strValues = strValues & "null" Else v = fld.Value Select Case fld.Type Case dbMemo, dbText, dbChar strValues = strValues & "'" & v & "'" Case dbDate strValues = strValues & "#" & v & "#" Case Else strValues = strValues & v End Select End If Next fld strTemp = Replace(strInsert, "{%2}", strValues) strFile = strFile & strTemp & vbNewLine .MoveNext Wend rst.Close End With If Len(strFile) > 0 Then Set io = fso.CreateTextFile(strName) io.Write strFile io.Close End If End Sub
View 3 Replies
View Related
Oct 11, 2006
I am trying to figure out how this code actually works. In the Field I see the following code:
IIf(Weekday([CollectionDate]-4)=1,[CollectionDate]-6,IIf(Weekday([CollectionDate]-4)=7,[CollectionDate]-5,[CollectionDate]-4))
In the Criteria I see: Like Date()
I know what the "LIke Date() does but what im trying to figure out what is the connection to the above....
Any Help would be greatly greatly appreciated!
View 1 Replies
View Related
Mar 29, 2008
Hi all!, i would really appriciate it if you could help me out. I have this complex query... in one table i have dates and costs associated that show when fuel was put into the system
such as:
1/1/2008 -- $200
1/20/2008 - $100
1/25/2008 - $150
I then have another table that has data when fuel was taken out of the system
such as:
1/2/2008 -- ($50)
1/3/2008 -- ($20)
1/4/2008 -- ($10)
what i need is a query that is able to show a running tab on the fuel tank and how much fuel is in it...
so for example if it could spit out a result such as
1/1/2008 -- $200
1/2/2008 -- $150
1/3/2008 -- $130
1/4/2008 -- $120
If this is convoluted i can upload a little database..
Thanks! (Here is the current query i am using but its not working right.
SELECT DatePart("yyyy",[purchase_Date]) AS AYear, DatePart("m",[Purchase_Date]) AS AMonth, DatePart("d",[Purchase_Date]) AS ADay, DSum("gallons_purchased","purchase_fuel_tbl","DatePart('d', [purchase_Date])<=" & [ADay] & " And DatePart('m',
[purchase_Date])<=" & [AMonth] & " And DatePart('yyyy',
[purchase_Date])<=" & [AYear] & "") AS RunTot, fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons, [RunTot]-[fuel_use_tbl].[gallons] AS cur_Value
FROM (tank_tbl RIGHT JOIN purchase_fuel_tbl ON tank_tbl.tank_ID = purchase_fuel_tbl.tank_ID) LEFT JOIN fuel_use_tbl ON tank_tbl.tank_ID = fuel_use_tbl.tank_ID
GROUP BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]), fuel_use_tbl.fuel_date, fuel_use_tbl.external_ID, fuel_use_tbl.gallons
ORDER BY DatePart("yyyy",[purchase_Date]), DatePart("m",[Purchase_Date]), DatePart("d",[Purchase_Date]);
View 14 Replies
View Related
Sep 5, 2006
I saw this code and was wondering how does it actually work?
I saw it in/connected to a textbox.
Private Sub savetime_BeforeUpdate(Cancel As Integer)
End Sub
View 1 Replies
View Related
Dec 1, 2005
Hi all
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example:
I want this: AUTO ONE CLARKSON CLARKSON WA AU006495
to be recognised as: car parts
or with this tranaction:
CLARKSON MINI MART CLARKSON553908
look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly.
thanks
View 3 Replies
View Related
May 11, 2006
I am not sure if I understand this...
I have MainTable, on which I base MainForm. I would like to have MainForm show only the records that have a null value in CertainField. If I write NullQuery to select only those records, can I redirect MainForm to NullQuery? Well, I know I can do that... but how does MainTable get updated with new records if MainForm is based on NullQuery????
Any help is greatly appreciated.
Tom
View 4 Replies
View Related
Jul 16, 2005
Let see if I can explain my goal. I've been fighting with this for some time with no resolve. In the included DB has a table that represents the fields that will be queried in the real DB. It shows records of one of many employees that were "tested" (shopped) on a date and whether it was a successfull shop or not. My goal is in three parts.
1)A query that goes through each employee and returns the records with the check box in SuccessYes checked (Yes) back to the last unchecked box, not including the unchecked box. In the example given the last (most recent) 5 records would be returned. If the shop of date 1/18/2005 was not checked, it would only return the last 2 records. Basically, I'm looking for a way to have a report that shows everyone's current successes in a row. It could be any number.
2)A query very similar but it returns only those employees that have the current successes in a row totalling 5 and where the field "Award5_Paid" is NOT checked (they have not been paid their reward). From there it should be easy to do one for the 10 in a row.
3)If I've got a form open (or...?) where I can place a command button, that when clicked, it appends the selected records with checks in the appropriate boxes for being paid. Example: If a set of employees come up in the query with 5 successfull shops in a row because the "Award5_Paid" field is unchecked, then I need to pay them. Click the button and the Award5_Paid field gets checked in THOSE RECORDS ONLY so that the next time I run the query, they won't show up and get paid twice. Issue? What's the coding or SQL or ??? that's run when clicked.
I hope that makes sense. I've been running around in circle trying to figure this one out. Maybe it can't be done, but if it can, I know some of you know how.
As always, thanks for the assistance.
-Jim
View 3 Replies
View Related
May 19, 2006
BACKGROUND
The current challenge I have in MS Access in really giving me a headache. I can’t seem to find an answer or indeed think “outside the box” so I am hoping there is someone who can help.
Below I will outline the design of my database, the problem I have and my proposed solution. My solution is “What I want to do” rather than “How I do it”. If you have the time and patience to look at my problem and suggest how I proceed, I will be really grateful.
CURRENT DATABASE DESIGN
I created a database that imports daily telephone data. The design below is a simplified version of the real thing but it contains the essential information needed to understand my database.
I extract data from the phone system for “Lines” (3 digit code) e.g. ‘301’,’302’ which each have a corresponding line “Description” e.g. ‘New Customers’, ‘Accounts Queries’. There are three main daily extracts (1) Inbound Calls (2) Outbound Calls and (3) Time. Each extract has either “Line” or “Description” as the unique identifier but not both.
There are 50 lines with matching descriptions and each is allocated to one of 20 teams. I have a query which links all three extracts, groups the data by team and date. Therefore this query creates 20 records (teams) for each day (date).
CURRENT QUERY AND TABLES
Tbl Line Lookup (50 records)
Line
Description
Team
Tbl Inbound Calls
Line
Date
Offered Calls
Answered Calls
Abandoned Calls
Tbl Outbound Calls
Description
Date
Outgoing Calls
Tbl Time
Description
Date
Answer Time
Abandon Time
Talk Time
Wrap Up Time
Qry Grouped By Team Daily
Line – linked to line in Tbl Incoming Calls
Description – linked to description in both Tbl Outgoing Calls and Tbl Time
Team – Grouped field
Date – Grouped filed
Offered Calls - Sum
Answered Calls - Sum
Abandoned Calls - Sum
Outgoing Calls - Sum
Answer Time - Sum
Abandon Time - Sum
Talk Time - Sum
Wrap Up Time - Sum
PROBLEM
Customer Services “Team” is made up of six lines (301-306) and Business Partners “Team” is made up of three lines (307-309). For each day my query creates a record for each of these teams based on how the line is allocated in Tbl Line Lookup.
The business had decided that line 304 calls are part of the Business Partners with effect from 01/05/06. If I change the team name associated with 304 in Tbl Line Lookup this will, incorrectly, allocate all information (from 01/01/06 to present) to Business Partners.
I need a mechanism to allocate 304 to Customer Services prior to 30/04/06 and to Business Partners after 01/05/06.
PROPOSED SOLUTION
I want to introduce a new field in Tbl Line Lookup called Effective Date.
Tbl Line Lookup (50 records)
Line
Description
Team
Effective Date
Then I want to introduce a new query which links the data in the three data tables and allocates the correct team name by comparing the date in these extracts with the effective date in the Tbl Line Lookup.
I would then run my original query (Qry Grouped By Team Daily) over this query instead of the original tables.
Please can someone tell me if it is possible to look up values in another table to determine the value of a field in the way I have described?
View 5 Replies
View Related
Nov 9, 2006
Hi,
This is what i want to do:
Have a button on a form that when pressed opens a file browse, allows you to pick a text file (May contain different values and in future the structure may change slightly), then import the text file contents into a temp table (2 fields, Setting & Value), then run a query to take different values from the temp table and put them in to different fields in to different tables linked to the same main record where the button is, then save the record and all linked tables data, and then delete the temp table, and display a message box saying something like done.
Each import file will be a single record, but has to many fields to be in one table
I also need to extract only part of Field 1 (Sample Below), the part after the = (150 in below)
0x6A,0x10,0x12 = 150
Thanks
Dan
View 14 Replies
View Related
Jul 20, 2006
does anyone know where i can find a dictionary of what VB words mean in plain english so i can write and adjust them.
View 4 Replies
View Related
Apr 27, 2005
Many will look at this and laugh but its real.
My Access Dbs are opened in hidden state and i dont see my forms.
This must be the cause.
I tried to hide the menubars using ghudsons code,i disabled the shift key. But i dont know what has happened.
I can nolonger view them. I have tried every method out and i think the only option is to repair OS, may be it will work.
I have enabled the database which i disabled the shift key but when i hold down the shift key, it loads but the forms are totally hidden, STRANGE! ijust see the grey access background and when i click on it twice from the task bar.It pops up.
I have tried to make another database, use the autoexec macro to call a form which enables all command bars, but this works only for this database and when i press the shift key down, this database hides the toolbars and i only see the grey access background. This is happening on EVERY DB ive developed on this machine. I thought it was the access that is totally corrupted but i tried to reinstall my office and i still get the same results
This is now a NIGHTMARE to me.
When db is shifted to another machine it works fine as before.
This is REAL but can i get some suggestions from you folks before i repair my OS.
View 1 Replies
View Related
Mar 21, 2007
trying to join two tables but get message
"relationship must be on the same number of fields with the same data types"
data types are same, numbers.
Primary table has just jobnoID that are all unique (primary table)
ie 6907, 6908, 6909 etc
second table "jobs by order" (secondary table)
has
unique orderno(order numbers) in column one say 69071 and 69072, 69073
and jobno in second coloumn say 6907, 6907, 6907 for all the above.
trying to link JobnoID primary table (one to many) to Jobno secondary table (many)
Also need to note. This database is a purchase order database. so am trying to load in all past data from excel. data is in already. so primary table above with jobno is an auto number so will generate the new jobno for us. but had to copy and paste blank fields into table to get records upto the number we are upto now, ie job number 7112. I have only put data in secondary table for
jobno's 6885 through to 7112, and even some of these have blank spaces.
Any ideas?
Also how do i do a screen capture and dump in here so you can see relationships etc, which would be a whole lot easier to explain. thanks heaps
Alastair:rolleyes:
View 4 Replies
View Related
Oct 18, 2006
I am building a database for a Real Estate business.
I have 2 tables tblPurchasers and tblVendors.
I intend to write a query that will match the requirements of the purchasers to the properties for sales (from the vendors).
My issue is this (best illustrated via an example);
I run my Query on the 1st April and four properties are produced as being suitable for purchaser 'A'.
Purchaser 'A' immediately deems properties 1 and 2 unsuitable and views property 3 on the 6th April. Property 3 is subsequently deemed unsuitable as well.
I re-run my qury on the 10th April and five properties are produced as now being suitable for purchaser 'A'.
Purchaser 'A' has already dismissed properties 1,2 and 3 so i don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Purchaser 'A' has already dismissed properties 1,2 and 3 so I don't want to show him these again ....... how do I just ensure that properties 4 and 5 appear ?
Something involving a yes/no box on a form would be the best method for me of implementing this (as it would fit in with the scematic of the database to date).
I imagine the properties (with a small photo) appearing and tick boxes headed 'Unsuitable', 'Viewed' etc be used to dismiss properties, which do then not re-appear.
MANY THANKS !
View 1 Replies
View Related
Aug 31, 2006
Detailed search cannot answer my time problem. Returning after long break in programming, I am trying to create a form to display TimeIn and TimeOut fields such that actioning an OnClick command (or separate tick-box), TimeIn field will hold current time for that record. Need to set the same for a TimeOut field. Both cases time always to be < 24 hours.
Am so rusty on MS Access putting default value as =now() produces an error!!
Any basic help to wind up the old mind always appreciated.
View 2 Replies
View Related
Jul 17, 2006
Ok, I have access on my computer but my boss does not. (I use my personal laptop). When I finish my project is there a way to allow him to use it without access? Like can I just make it a windows application? If so can somebody please point me in the right direction? Thanks
View 14 Replies
View Related
Sep 17, 2006
Hi:
I try to get the value in a real time. There are two subforms inside a mainform. First user enters value into mainform. Than comes to subform1. And after that on the basis of a choice in subform1, the 2nd subform is open. In 2nd subform i create a listbox with that query.
SELECT [tbl_Events].[PPVVOD_Outlet] FROM tbl_Events WHERE [tbl_Events].[ticketnum]=[Forms]![tbl_PPVResearch]![ticketnum];
But it shows empty. [tbl_Events].[PPVVOD_Outlet] is a subform1
View 1 Replies
View Related
Jan 20, 2006
History:
MS Access 2003
Database up and running over 5 years
Compact and repair daily
250 end users
Security screen with log on to database
Problem:
User signs on and everything runs smooth, then all of sudden can’t access some reports or forms. I call our IT people, usual fix is resetting the profiles. Now sometimes this works and sometimes it does not work. This problem just started a few weeks ago. I try to isolate the problem. I sign on his computer and have no problem. I have the user that is having the problem to sign on another computer same thing, he has problems. So to me it’s the user profiles right?. The IT people think it’s the database. I have tried about everything I can think of, but still have the problem. Also all the other users are up and running fine.
Now the only thing that I can say is, that we are getting up dates from Microsoft on a daily bases now. Our IT people would run them at night instead of during day. Do you think that could do anything to the database? I have spoken to them and they mention its security updates.
Can you give me any other direction that I can go? We are going to have a big pow wow meeting next week, I’ am trying to have something that I can go on.
View 3 Replies
View Related
Feb 6, 2006
Hi there,
Is it possible to control a lighting system from Access using a combination of VBA code or Active X add ons with some specialist hardware and cabling attached to the PC, etc ?
To be more specific, I've designed a Snooker Hall Management program and want to be able to turn the lights on over the Snooker table when you start a game in an Access form...My application has 8 tables but could be expanded obviously.
I'm happy with the Access side of things (first attempt at a real world solution, etc) but to be totally polished a hardware interface to control the lights would really create a 'wow' factor...
Any help or direction would be greatly appreciated :)
Regards
dazza61
View 4 Replies
View Related
