Howdy. I have a question on design of tables. Most of the databases I have seen developed here (and in books) assume that there is some kind of individual input of data. Thus, one major table for input (of various sources) worked well on my other projects. This one doesn't seem to fit the mold.
I have Excel/CSV files from five different vendors. Each deals with a different kind of data. That is, while all of them will have a few common elements, there are several significant differences in the other fields that I cannot bring them into the same table. For instance, in one table one field might be "Size" and refer to inches, while another table would have size, but it refers to the portion of the page (1/4, 1/3, etc.). Thus each of the five vendor tables have unique fields.
Tables:
Input tables:
tblTV
tblNPP
tblRAD
tblOOH
tblONL
Ref Tables:
tblSEGMENT
tblMEDIATYPE
tblDMA
tblDAYPART
For instance, fields for tblTV
TVID
Station
DMA
Daypart
Program
Length
DateStart
DateEnd
TRP
NumberSpots
Cost
MediaType
Cons/Bus
CreativeDescript
CreativeOffer
Segment
So, my questions:
1. Is it acceptable to have five input tables, rather than one?
2. These five vendors will update their input data on a daily weekly basis. This, there will be changes to some fields and addition of many new records. What is the best way to handle this?
3. In addition, we will have 2-5 people updating several fields (i.e. last five in tblTV above) that the vendors cannot supply. Will forms be the easiest way to accomplish these updates?
3. Relationships will be critical in getting this set up correctly. Any suggestions?
Any kind of direction, suggestions will be very much appreciated.
I have a form that has three different pull down menus. When I click a button on the form it opens a second form that is filtered by a query. In order to allow the user to leave a pull down menu blank the query has 6 different columns. Three are headed with the field name and three are headed with the form reference, under the form reference it either says Is Null or Is Not Null. I have a total of 8 options in order to cover all possible scenarios. I would like to be able to add more search parameters but my query is getting ridicules. :confused: Is there a way to build the same thing with a code?
I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.
EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.
I am trying to creat a DB that can potentially recieve input from a serial port which has a unique ID on it and updates the corresponding feild within the DB. I don't need to demonstrate full functionality (although if I have time I might give it a go!).
What I want to show is that the DB can accept a unique ID from this external source. The ID will just be to identify a user and a peice of equipment, potentially the feilds in the table would be updated with information like, time of session, duration, speed. I am assuming I may need to use VB to do some programming and create an incoming event. Just wondering what the most logical solution is. I want to bring up an interface when an input is recieved, like a user interface or pop up which says eg. "Hello Jim". Then it is possible to demonstrate that they have been detected.
I am fairly new to Access, and need some help with what I thought would be an easy project. I have an Acceess table that contains data on settings for machines in my plant. There are columns for part #, Machine #, part type, etc. All in all there are about 20 different columns. Now what I need to do is have a form where a user can enter one or two fields, and print a report that contains all the information from all 20 columns.
For example, the user would enter Part number "AB1234" on machine "15-1" and would get the following data from the table:
Part # part code Equip. Temp height etc AB1234 A 15-1 100 5.3 ... AB1234 B 15-1 200 6.3 ... AB1234 C 15-1 250 7.3 ...
They could then somehow choose exactly which record they wanted (example : ABC1234 B) and print a report that contained just the data from that selection.
I hope I explained this clearly enough. Basically I want a user to be able to enter 1 or 2 selection fields and then be able to print a report that contains info from 1 row in 1 table. Any help would be appreciated. I am somewhat new to this and havee a basic understanding of Access and A moderate understanding of VB. Thank you in advance for your help.
I have a form that contains 5 fields and want to ensure that duplicate information is not entered. I am aware that when creating the form you can set Duplicate Records to No but each record has an AutoID which I assume means that even if the information is the same it will not register as a duplicate record due to the different AutoID.
There should only ever be 5 records entered per day. One for each region
I have created a Sanity Check that is displayed as a sub form that shows if the relevant information has been entered for a given day in Bookings and Backlog but this doesn't stop the user from putting in the same information twice. i.e. the User starts inputting the information gets called away and then trys to put it in again but doesn't check the sub form.
The Table in which the information is stored is called Bookings and Backlog.
The 5 fields on the form which is called Bookings and backlog are
ID (Autonumber) Date (Short Date) Combo26 (Text) (5 Regions:- North, South, East, West, Central) Bookings (Number) Backlog (Number)
I have put the following statement in AfterUpdate on the Combo26 field
If Me.Date = DLookup("[Bookings and backlog]![Date]", "[Bookings and backlog]", "[Bookings and backlog]![Date]=[Date]") And Me.Region = DLookup("[Bookings and backlog]![Region]", "[Bookings and backlog]", "[Bookings and backlog]![Region]=[Combo26]") Then 'Define Message Box MsgBox "The Region you have chosen already has data entered for this day." & vbCrLf & vbCrLf & "Please check your records and amend were neccessary.", vbCritical, "USER INPUT ERROR" Else End If
However I'm not to hot with writing VBA and probably got something wrong somewhere as it doesn't seem to do anything and lets the user progress regardless. I have tried amending the above script and asked it to look at just one field but this just seems to give the message regardless of the information selected in the Combo box.
Can someone give me an indication as to what I have done wrong so I can put it right and in a language that is easy to understand so that if I have a similar issue later on I don't need to post another question.
Essentially I am trying to build a macro or even a VBA script which would open an initial form with various data entry options, and then depending on the options chosen it would open specific forms sequentially with some preset data values.
I can go into more detail if needed; however, the problem is at my work (lab in an industrial plant) my coworkers and I waste a lot of time inputting data as our forms just include every field (which only around 10% of fields are utilized for each entry) because certain circumstances will dictate what fields need to be filled in, so for each entry most fields are left blank. These circumstances are entirely predictable as to what fields I will need for each entry so I thought I would easily be able to figure this out on my own but I have been unable to do so.
I want to run a query that runs off of two list boxes on a form. However, I want both these parameters to filter a single field in my query. Why I want to do this is I have several types of accessories made by several manufacturers, both included in the "Description" field. I don't want to include an entirely new field for manufacturer because it's only 4 out of about 5 items that need this.
Is there a way this can be done or will I be forced to include a manufacturer's field.
Was wondering if it is possible to create a query or another method that would calculate future dates based on inputted info ?
For example a person inputs on a form a date completed (06/14/07) and then also selects a frequency of when this has to be revisited....monthly, quarterly, semi-annually.
So based on the date completed that the person inputs I'm trying to get the date if they select monthly of 7/14/07 (using above date example).
I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).
I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.
Is this possible and how do I go about setting it up?
I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.
Apologies, but I am still getting to grips with access to be honest.
I am looking for an elegant way to retrieve a number from a table and display it in a form, where the number is determined by two values that will be input by the user to the form.
In particular, I want to be able to enter into a form:
- a date; and - a class of asset
And have the relevant rate of depreciation for that asset class and that year appear in the form.
Sounds trivial and I guess it can be done in a number of ways. I have tried DLookup. It looks like it should work, but I have not been able to get the syntax such that it will accept a control value as a field name in the first argument of DLookup. (This would involve a table much like a spread sheet with a column for each year. I guess such tables are frowned upon and I have also heard negative comments about Lookups.)
I have experimented with concatenating the two fields into one such there is only one field to search upon. Seems to work. The table would only be updated annually and would only be a few thousand records.
I have experimented with having two joins between tables, but that did not seem ideal.
Perhaps a query of a query?
Perhaps a macro?
(Assuming I can get the form to work I would also like to create a report that lists assets and their depreciation.)
I have a form with several fields and combo boxes on which the user will select different criteria to generate a report.The default value for each of these fields/cbo's is "*", so if the user changes nothing from the default, I execute a basic "SELECT *" query.
If however the user enters some value (selects a specific date range, customer number, salesman,etc), I would like to 'easily' modify my query to accommodate the entered search criteria.Trying to construct SQL when there are many search options like this is difficult. I took a stab at using QueryDefs but I'm struggling to find example VBA code as an example.
I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.
I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :
Private Sub Command30_Click() On Error GoTo Err_delete_Click Dim stDocName As String Dim intResponse As Integer intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team") Select Case intResponse Case vbYes CurrentDb.Execute _
My report generates base on 2 date inputs(from textboxes) namely, ProcStartDate & ProcEndDate & a combobox(cmbRptSupplier) to select the supplier:
Code: Private Sub btnReport_Click() Dim strDocName, strWHERECondition As String strWHERECondition = "" If Me.cmbRptSupplier = "" Or IsNull(Me.cmbRptSupplier) = True Then
[Code].....
When I leave my cmbRptSupplier blank (so that I can leave supplier out of my criteria), & fill in 1/2/2013 for ProcStartDate, 14/3/2013 for ProcEndDate, I get ALL the invoices displayed in return for my report. Even with those before 1/2/2013.
I am currently using the column history to log the history of inputs into a memo field.
But i now need to swap how my database runs and now require a combo box to have the same history function, as this is for a status updates and i require users to only input certain status's.
I no that I cant use the columnhistory command with a combo box.
When selecting a status from the combo box it automatically update the memo field (which will be hidden on the form) so the column history function works.
I have a form and a subform with a master/child relationship set based on the primary key of each underlying table. All good there.Now, I want to use VBA to create a filter based on a set of inputs via combo boxes. But the filter must filter both the Parent and Child records.Example. "Show me only records where both only the Parent.Field1 = "string" and Child.Field = "string".I can do this in a QRY as follows:
SELECT Projects.[Project Number], Lessons.[Actions Resolved] FROM Projects INNER JOIN Lessons ON Projects.ProjectsRecordID = Lessons.ProjectsRecordID WHERE (((Projects.[Project Number])="AU-2102421") AND ((Lessons.[Actions Resolved])=True));
But, if I make this as a record source for the Parent Form, then the records in the Parent Form are repeated for each individual record in the Child form.
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:
Function GetAllFiles() Dim fd As Object Dim strFilter As String Dim lngItems As Long
Const msoFileDialogOpen As Long = 3 Const msoFileDialogViewDetails As Long = 2
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I would like to know which way is the best way to import excel data from multiple sheets in to multiple tables in access.
For example data from Sheet1 -> Table1, Sheet2->Table2, Sheet3->Table3 etc...
I have tried using this: Cmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Table1", "C:Importedfile.xlsx", True, "Sheet1!"
Ironically, data from Sheet2 and Sheet3 seem to be properly imported in to table2 and table3, but some of the data from Sheet1 seems to be missing in Table1 after import.
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _ & " FROM Table1 " _ & " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _ & "FROM Table2 " _ & " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
I have 3 tables made up of ID and NAME and other parameters. NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.
Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
