I have a store database containing multiple values about a single product, and separate table on color that this product comes in and in another table are quantities this product is available in. How can I make relationships between the tables so it works and I can make queries from it?
View RepliesI've built a pretty extensive database for work thanks largely to the folks on this board. But I'm stuck. I have a report to track inspections and it works fine, but I'd like to filter it down by AFSC (a coded expression the AF uses to denote career fields, i.e. 2A5x1x is Aircraft Maintenance.) The report's queries come from a couple of sources to retrieve the requirements for the report. For instance that AFSC has to start w/ "2A" and the labor code needs to start w/ "1" and not be "120". No problem. The problem I'm having is that the first 'x' (from the AFSCs below) is based on skill level (can be a 1, 3, 5, 7, or 9) and the last x is sometimes there and somtimes isn't.
For this particular report I've broken down the AFSCs down to three different formats. They are as follows:
2A5x1x (i.e. 2A531B or 2A571)
2A5x3x (i.e. 2A533A, 2A55B, 2A57C)
2A6(or 7)x1(or 2,3,4,5,or 6)
The report is designed to show who is due an inspection and it works fine. But I need to try to make it a little more user friendly so that all the various workcenters can trim it down to view just their career fields. Right now it reads from a query.
Here's the SQL for it.
SELECT qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC AS FilterAFSC, [Employee List Table].[Labor Code] AS FilterLaborCode, qryPEDueUnion.[Inspection Type], Last(qryPEDueUnion.Date) AS LastOfDate
FROM qryPEDueUnion LEFT JOIN [Employee List Table] ON qryPEDueUnion.[Main Assessee] = [Employee List Table].EMP
GROUP BY qryPEDueUnion.[Main Assessee], [Employee List Table].[Employee RANK], [Employee List Table].[Employee NAME], [Employee List Table].AFSC, [Employee List Table].[Labor Code], qryPEDueUnion.[Inspection Type], Left([AFSC],2), Left([Labor Code],1)
HAVING ((([Employee List Table].[Labor Code])<>120) AND ((qryPEDueUnion.[Inspection Type])="PE") AND ((Last(qryPEDueUnion.Date)) Between DateAdd("m",-19,Now()) And DateAdd("m",-15,Now())) AND ((Left([AFSC],2))="2A") AND ((Left([Labor Code],1))=1))
ORDER BY Last(qryPEDueUnion.Date);
Let me know if you need more info. I'm new to posting on this board and haven't quite found out how to attach files....Plus this db is pretty big and I'd have to fool around w/ a copy of it first to make it small enough to attach.
Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.
Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False
[Code] ....
Error
Microsoft Visual Basic popup
Run-time error '3061'
Too few parameters. Expected 1.
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?
View 1 Replies View RelatedI am building a database for a martial arts program for older/elderly adults. I have a section that measures their physical capabilities. I need to create a text box that will fill in if their abilities are below average, average, or above average on one of the tests. The problem is, the test measurements are based on age and a range of repetitions.
For example, men 60-64 need to do 14-19 reps of the exercise to be average and women of 60-64 need to do 12-17 reps of the exercise. Men from 65-69 need 12-17 reps and women of the same age need 11-16 reps of the exercise. I have a box for their age (on a separate table from the rest of the information) but I need to figure out how to allow someone to fill in the number of reps a person did and have if they were average, above average, or below average filled into the last box based on that number, their age and their gender.
I tried a combo box, but I can't seem to figure out how to create the table to get it to work.
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.
I need to redesign a database to include more complex relationships between 3 variables non of which are mutually exclusive.These are my 3 tables/fields. Each field is unique in its table.
T_Project.JobNo
T_Accession.AccessionNo
T_Reports.ReportNo
There is no clear relationship between the variables - ie they can all be 1 to 1 or 1 to many or do not have to exist at all. So I realise now (after 14 years of working with this data), that I need to have what I would think of as holding tables between the primary tables that hold combinations of the variables. Do I do this in one table that holds all 3 variables in non-repeating combinations (although this would need to allow nulls) or do I do it in 3 separate holding tables?
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
I have a table that contains dates (Saturday through Friday). When I set up my query, I would like data returned where the date in a field is between the Saturday date and Friday date. Is there anyway to do this n the query without hard coding the dates in and manually changing the dates every week?
View 1 Replies View RelatedWell I'm hoping someone here can point me in the right direction. This is my problem... We've got about 50 Access databases scattered across the network. Each network database contains the same tables and fields, but of course, each one contains different information (one network database is for DepartmentA, another network database is for DepartmentB, another network database is for DepartmentC, and so on).
What my boss wants is a single table/view that displays all the information contained in every network database table. The other part of this is that my boss then wants to be able to make changes in this single, all encompassing table/view and have those changes be pushed back out to the individual network databases. Is this possible? And if so, can someone help point me in the right direction? I'm still a little new at Access, but I can manipulate VBA code and all that if need be (I'm learning more and more as I get deeper and deeper into this project).
Thanks!
Lucid
I am currently designing a database to solve our organisation's training requirements. The db needs to be able to identify an individual member of staff at any particular site and list all the medical devices that they have training on and to what level of competency. So far I have five separate tables:
Staff details
Site
Devices
Competency/Risk
Equipment
By using unique identifiers within each table, we can, in theory, comply with the requirements.
Now here is the problem. Having talked it through with a colleague, we have two possible options.
A. Using a form based on the staff member, combine the salient details and, where needed, create additional fields (such as dates and tick boxes) to record odd items (this is my preference)
or
B. Based on the site, create a make-table query and form a table, sorted by site and then staff, listing all the equipment and competency levels. Given that some staff will work at multiple sites, the table will probably contain over 6500 records with lots of duplication (you can see my bias!)
Which solution is probably the best or should we be looking at solution that combines elements of both?
Thanks in advance
David
I have to assign multiple table validations to my table but they are not even working separately.
1) I have two combo box controls: [Tocolytics] & [multiple_Tocolytics], 3 check boxes and 1 text box.
Rule:
IIf(([Tocolytic?]="Yes" Or [Multiple Tocolytic?]="Yes"),([Indomethacin]=True Or [Nifedipine]=True Or [Nitroglycerin]=True Or Len([Other])>0),"check one of the boxes")
2)Two combo boxes The answers are mutually exclusive. If one is yes then the other must be "No" or "unable to determine"
Rule:
IIf(Oxytocin Infusion - no PPH]="Yes",([Oxytocin Infusion - if PPH]="No" Or [Oxytocin Infusion - if PPH]="Unable to determine"),[Oxytocin Infusion - if PPH]="Yes")
I have a table named [Brand] and several tables named [Brand@@@Models], one for each field in the Brand table.I want each field from the [Brand] table to be related to it's specific Model table. I thought like that so I don't have a Brand#1 related to Brand#2 models, etc.. I didn't want to have all the models for all the brands in a single table, was I right to think like this?
View 6 Replies View RelatedI have quite a limited knowledge of MS Access (2007).
My question is; How do you use sections of one table in other tables?
For example: In tbl_One I input a users Name and Age and then say Weight, Height, BMI, etc.
Then in tbl_Two it uses the same Name and Age in tbl_1 but has other information such as Job, Skills etc.
This is not my actual database, however this analogy works, and they must be in separate tables, even though above they could all just be in a single table.
I have a big number of sales staff..and broken them into different teams.
I have one TABLE1 with all their names, i need to have table which links to TABLE 1
All the fields are the same except team field. Therefore i need table 2 to only show me all fields and TEAM ALPHA only.
In total i got 46 teams therefore it is big.
I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record.
I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:
Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.
'------Code I'm Currently Working On-------
Dim intX As Integer
Dim EventVal As Integer
Dim strX As String
intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal <> intX And intX < 6
intX = intX + 1
Loop
strX = str(intX)
Dim E_Name As String
Event_Name = [Event strX ]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]
'-----Old Code That I'm trying to simplify-------
If Event_List = "Event 1" Then
Event_Name = [Event 1]
PMT = [PMT 1]
Receipt = [Receipt 1]
Payment_Type = [Payment Type 1]
PIF = [Paid In Full 1]
Camp_Site = [Camp Site 1]
Cabin_Number = [Cabin Number 1]
ElseIf Event_List = "Event 2" Then
Event_Name = [Event 2]
PMT = [PMT 2]
Receipt = [Receipt 2]
Payment_Type = [Payment Type 2]
PIF = [Paid In Full 2]
Camp_Site = [Camp Site 2]
Cabin_Number = [Cabin Number 2]
ElseIf Event_List = "Event 3" Then
Event_Name = [Event 3]
PMT = [PMT 3]
Receipt = [Receipt 3]
Payment_Type = [Payment Type 3]
PIF = [Paid In Full 3]
Camp_Site = [Camp Site 3]
Cabin_Number = [Cabin Number 3]
PLEASE HELP!!!!
I would like to declare Global or Public variables from a table so they can be added to or edited easily. I had the following function to do this with Alpha Five but at the moment my lack of knowledge of Access VBA is making this task difficult.
Here is the function that I use in Alpha:
FUNCTION udVars AS A (udTabName AS C )
dim codeStr as c
t=table.open_session(udTabName,file_ro_shared)
t.fetch_first()
while .not. t.fetch_eof()
[Code] ....
It opens a table reads in the records and then makes them into a string the string would look something like this:
"Public EuroRate as Single = 0.885"
I would then need to use this string to declare the variables but not sure what command to use - I was looking at the Eval function last night but couldn't get this to work...
The table would hold strings for all the above so you would have:
My_Var (variable name)
My_Typ (type of variable e.g. Single)
My_Val (value e.g. 0.885)
The table would hold as many variables as required, some would be dates, some paths for making directories and some would be numbers.
Access 2010 vba - I'm trying to pass a start date and end date to a date field in a make table query, and use the 'between' operator on that date field.
So I have a criteria on the date field like this "Between [dtStart] and [dtEnd]" and if I run the query manually it asks for 2 values and then works fine.
Here's the code I'm trying to run:-
Set qdef = db.QueryDefs("qryTest")
qdef.Parameters("dtStart") = StartDate1
qdef.Parameters("dtEnd") = EndDate1
Set rs1 = qdef.OpenRecordset(dbOpenDynaset, dbSeeChanges)
and I get the error "3219 Invalid Operation" on the last line.
Please could you advise me if tables have to be linked in order to perform a query on multiple tables? For example searching for a date and matching records on more than one table at the same time.
View 1 Replies View RelatedI have three tables, with the following data (fields separated by "-" here):
Unit - Customer Number - Customer Name - Type of A/R - Total A/R
Unit - Customer Number - Customer Name - Type of A/R - Total Overdue
Unit - Customer Number - Customer Name - Type of A/R - > 90 Day Balance
I would like to combine these tables into one table with the following field names:
Unit - Customer Number - Customer Name - Type of A/R - Balance
When I append the data, it comes out like this:
Unit - Customer Number - Customer Name - Type of A/R - Total A/R - Type of A/R - Total Overdue - Type of A/R - > 90 Day Balance
I would like this new table to include all rows from the original tables, sandwiched on top of each other. As far as I can work it, the Append Query only adds the data as add'l columns; not as add'l rows. I tried changing the field names entirely, so that the final field in each table is called "Balance." However, I still get the same result as above - the columns are added, instead of rows. I swear there's a way to do this, I just can't remember it!
I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:
1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill
Here are my excel formulas that for the life of me I can't get to work.
Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))
Can I have calculated fields that refer back to a calculated field?
I want to make a table where in I have to input atleast 10 numbers in one reference ID which there is a start time and end time.
A form with inputing one reference ID and then adding multiple numbers at once and saving the data with a start time and end time. Is it possible to capture the time for that specific reference ID?
How to do it or a sample tracker on measuring it.
I have a risk table containing risks and risk owners (many owners for one risk), meaning that I have two risk owners columns. What I would like to do is to connect both risk owner columns (containing the name IDs) to the Names table. But Access does not allow to connect more than 1 column to another.
See the attached file...
How can I import from multiple tables into one table without replacing the destination table.so that the destination table consist of old data and new imported data.
For example: I have table A with date 130205 and I need to import from table B and C which the dates are 130206 and 130207. Those 3 tables already have the same column numbers and data type.
I'm trying to create a database at work keep track of projects I'm working on and all the different events that happen during the project.
I want the main table to be customers, which will include the job number (as the primary key) name, address etc. Then I need other tables that have information about the building permit that will include dates city names etc and then another table that would include information on our sales people and so on, there would be maybe 5 tables that all connect back to the customer table.
I have tried this several times and keep running into problems, I'm sure it has something to do with the relationships. I'm missing something. I can create 2 tables and it works fine but once I add a 3rd it wont work.