I have a situation where i need to import a table in Access which is in Excel, After importing i need to know how can i break it up differently keeping relation of FK and PK intact: E.g.
Excel Sheet:
Name Biz ID Biz Name Address1 Address 2 Address 3 OrderNo Order Date
Person Record
ID(PK) Name Biz ID Biz Name Address 1 ....
Order Record
OrdIDPK ID FK OrderNo Order Date
How can i distribute it?
I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
View 4 Replies View RelatedLet's say that a table has been broken in two and I'd like to rejoin them with a query. Let's say 100 records in table1, 20 records in table 2. Here's a random example, I just made the names up off the top of my head:
PersonTable (100 records)
Person # (unique key)
Name
Address
SpouseTable (20 records)
Person # (lookup wizard to Table1, cascading edit/delete relationship, 1 to 1)
WeightOfSpouse
HeightOfSpouse
Many people aren't "married" and so have no corresponding value in the Spouse table. If I do an inner join on the two tables, on Person#, then it will cut the total # or returned records in the query to 20, to match the total in SpouseTable. Essentially, I want to append the results of SpouseTable to the end of the corresponding rows in PersonTable.
Usually, a lookup column looks like this - "Take the value stored in this lookup column, use it to find some information in another table, and then display the information from that table in this table."
I need to Take the value stored in this lookup table, use it to fill two separate tables depending on ordertype. If the order is type1 I fill table 2 if order is type 2 I fill table 3
I have a database with 3 tables:
Table 1:
ID autonumber primary key
ORDR (numeric value increases by 1)
Table 2: Oders printable info1
ID autonumber primary key
ORDR (linked to Table 1 )
Table 3: Oders printable info2
ID autonumber primary key
ORDR (linked to Table 1 )
It's a piece of a large dabase and hard to explain, but I have to enter specifinc information in Table 1 then with relationship enter that information in Table 2 or Table 3 depending on the order type. Table 2 and 3 hold other information that is used to generate a report. Table 1 provides start unique identifier increased by 1 like, ORDR-001, 002 003 etc.
I never have to enter or should I say link same order number from table1 to both. Would like to prevent it by acident too.
Do I need a junction table to make it work? Is there a better way to make it work?
Thanks.
I am currently in the middle of creating a database as a means of keeping records of vouchers codes. When someone uses a voucher, I will then be able to input the name of the customer with the voucher code in the form.
This is meant to do two processes:
1. Keep a record of who has used a voucher
2. Check and validate the voucher code (the codes are kept in a table).
Now, I have created 90% of the db to input the customers details etc BUT...I am struggling for the validation part. Ideally, the db would also remove the said voucher code from the table so the same voucher code can not be used more then once.
I was going to "pre-install" the voucher codes in the db and then print off the vouchers for distribution. But I am basically tying to make the system so that it cannot be abused (for obvious reasons)...
How to extract the comment system from the Contacts Web Database template found in Access 2010? I can upload it if you like.
I would like to use it in a standard desktop database to track notes and comments in my records.
I am able to convert it from web to standard, but it still appears to use data macros or something, and my knowledge on macros is limited.
I just want to keep a history of my notes for each record, with the most recent always listed, and the comment system in the Contacts Web Database template seems perfect for that. I just don't know how to implement it into my desktop database.
Hi!
I'm new to MS Access databases.
I'm trying to buil a database of a kind of elements. Every element has it's description (name, number, etc), but there are other parameters, which I want to add. I'll try to picture this:
Element_Table--
|
- Date
- Number
Element_Additional_Info--
|
- Data1
- Data2
For every element in Element_Table I want a seperate table Element_Additional_Info.
I tried to do this with relations, but for every element I have the same data in addition info.
Can anyone help me??
Thanks in advance...
--
Best regards
fahur
I've been stuck on this for hours. I have to create a relational database on a small company and when i try to create a query on anything i only get one record showing up, with no criteria. This is my relationship
http://img156.imageshack.us/img156/1809/relationshipsbi0.png (http://imageshack.us)
Any help would to make this relationship work would be greatly appreciated, i need to go ly down
I'm pretty new on access but I need to get a project done for my office which is a call center, we are trying to identify and quantify the reason our agents are calling our support desk, and I'm trying to set up a simple database that our support agents will feed up.
I have a table (A) that contains AGENTNAME, AGENTID and SUPERVISOR (every agent is assigned to a supervisor and there are four supervisors and around 60 agents) the primary key on this table is autonumber. I have another table (B) that is a lookup table and contains the reason CODES (Ex, GRAL INFO, RATE, ETC), there is another table (C) that is filled up using a form that pulls agents name from table A using a combobox, also pulls the reason codes from table B and fills date and time also. the problem is when I try to set up a report using all this data, I cannot get the SUPERVISOR name and AGENTID on such report, I think this may be related to the tables relationship and I´ve tried several options but cannot work it out.
Can you please help, any help from your vast knowledge will be greatly appreciated. Thanks in Advance
i have a table set up in a many to many relation with each other.
it is
tblProducts
productId PK
productcode
Description
Status
tblLink
ProductId PFk
Location PFk
tblLocation
LocationID PK
Location
QuantityIn
DateIn
PONumber
I have created a query that shows me the product by location and a query to show me locations with product...What i am trying to do is have a main for that show me the product and insert a list box(Form Control) that show me the locations and the quantity for that product. the problem i am having is that i cant get the query to only show me the results for the product on the main form it shows me all the products on all the locations on the list box.
i have 2 tables
1. Actors Table
2. Movies Table
i create a third table so i put manually the id of an actor than the id of the movie belong to this actor.so in the third table we could see the movie more than one time because there are many actor in the same movie..could i get the result in the third table or anything but with an easier method because it takes too much time to finish it i have more than 1000 film.
I am trying to create a simple store keeping In and Out inventory database using Access, I thought I had made it but looks like I am missing something here.
The store works on SRV (Store Receiving Voucher) and SIV (Store Issue Voucher). Products will be added based on SRV and will be issued out based on SIV. So far I have created the tables as you can see in the figure. One thing I am not understanding is where to keep the record of the Current Quantity of each product, lets say an Item has been added or issue out, it should be added or deducted accordingly from that specific products overall quantity. Right now I have a sample field within products table as you can see with the name QtyOnHand but that doesn't seem to be logical.
I new to Access, I have used MS Works spreadsheets and database. Im working on a database for a non-profit to enter weekly donations.I have tables below:
1. DonorsT (names...) table
2. FundT (names...) table
3. SubFundT (names...)table
4. DonationT table (for cash, check, checkNumber ext.. )
5. DonationToFundsT table.
The Relation set up:
DonorT DonorID PK (1 to many) DonationT DonorID FK
FundT FundID PK (1 to many) SubFundT FundID FK (so the main Funds can have many SubFunds)
Because one donation can be split to many Funds/SubFunds:
DonationT DonationID PK (1 to many) DonationToFundsT DonationID PK
SubFundT SubFundID PK (1 to many) DonationToFundsT SubFundID PK
My question:
As you can see donations can only be recorded to SubFunds:I can work around this by having the first SubFund name be the MAIN Fund nameBut I was hoping there was a way to enter donation to the main Fund and the SubFundsExample with this set up:
General Fund
General Fund $100.00
Repairs $50.00
Total to General Fund $150.00
What I would like
General Fund $100.00
Repairs $50.00
Total to General Fund: $150.00
Because this data will be entered by people that dont work with Access much Im trying to make it simpler for them.
I have two forms ( main and Sub) . Every time I want to enter a new record the main data goes blank (not required) and the sub data goes blank for new data ( required). The new subdata is related to the main data. This way I have to retype all the same data in the main form which I wish not. Need making the main form data stays so I only type the subform.
View 1 Replies View RelatedHi I have a table that looks like this
ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------0
itemCode2-------------------0----------------2--------------1
itemCode3-------------------0----------------2--------------1
As you can see in the year 2010 items 2 and 3 go down from qty 2 to 1. What I am trying to do is to keep track of everything that was ever shipped to the customer. So with that in mind the above table is showing that Qty-2 was ordered in 2009 and Qty-1 was ordered in 2010. I want to add these as I go along. So my desired table would look like the following
ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------1
itemCode2-------------------0----------------2--------------3
itemCode3-------------------0----------------2--------------3
in this table 2010 shows Qty-3 which means 2 was present on site in year 2009 and 1 more was added in 2010 to make the qty 3. I want to write a storedProcedure or something similar to convert the first table into the second table. I said storedProcedure because I am used to doing this in SQL Server.
Data.
I have one table which holds the answers to a medical questionaire. 300 questions to be exact. This table has a Primary Key which is just an autonumber.
The problem I have, is that the questionaire results will be updated from time to time. Not the whole questionaire but maybe a couple of fields. Is there a way that the user can go back to the questionaire make the changes needed and then save, but at the same time still keep the original results in the table, but assign a new autonumber? The reason is that they do not want to fill out the whole questionaire again just to make a small change, but I need to keep all the results going back in history!!!!!
Sorry if I havent explained this perfectly. I have looked at the appen query and the update query but not sure if this would be correct procedure.
Thanks in advance. You dont have to tell me exactly how to do it but a hint in the right direction would be nice.:)
I have two tables (one is a query)
Table 1 (query based)
EMP_ID
Prev_Emp_ID
EMP_ID_DDSK
332-123
1
500
332-133
1
501
332-144
0
332-156
1
502
332-654
1
503
332-456
1
504
332-967
0
Table 2
Res_Numbers
Num_Of_Employees
500
10
505 - after 5 numbers are placed
Will be added for next hire
My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)
Example: If field "Prev_Emp_ID" = false
Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".
Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".
I want to take a report which covers sales figures and e-mail to those people that have had sales in the period. I only want each person to get just their records, not the whole report. Any ideas how I can do it automatically, starting with a query of records or a report ? ?
I know how mto e-mail a standard report using a macro !!
Hello all,
I need your help! Iam breaking my head for a day almost to solve this problem.
This is a timesheet application in MS Access in which I have a MS Access table with the following columns:
1. PersonID
2. SkillID
3. EntryDate
4. MON
5. TUE
6. WED
7. THU
8. FRI
The work hours for a person is entered once a week, at the end of the week. The person's SkillID can change but not in the same week. For ex a person scheduled as a Welder has to work as Welder for that week.
So, I need to find now all the PersonIDs with more than one SkillID in a week and flag them as errors.
Pls check the attached image. The first entry with EntryDate as "06-May-05" and Person_Code as "MK0259" repeating with 3 different Skill Codes. Then this is a problem which I want to hight light. Hope I explained clearly.
Can someone help please? Desparate!
Thanks in adv.
sgmuser!
Ok I have heard many times that you should never save a calculated field in a table. I know this is breaking the rules but can someone tell me how to save a specific combo value change to a table? and avoid all the rest?
Here is my problem, I was asked to have a combo box that has 5 choices and when the default is changed to another choice, I must have the date saved for reference. But the date is only supposed to be saved when a specific option in the box is choosen. Such as
Default: basketball
was changed to football
I can do a timestamp but don't know how to change it when a specific option is selected. It just changes when it is updated , even when I change it to baseball
Im actually saving it but can't annoy the other options...
Can someone help me out here PLEASE
I have a report - I have a agent header and code header.
It should look like this:
Jeff Moenning Agent Header
1-Active Code Header
Report Details
For some reason the first page just has Jeff Moenning and nothing else - after that the pages are correct. They are breaking when the agent changes and also when the codes change within the agents.
Hi All,
I have a field with a bunch of records. All is formatted the following:
CAN-2007-US-00001
CAN-2007-US-00002
CAN-2007-US-00003
CAN-2008-US-00001
CAN-2008-US-00002
CAN-2008-US-00003
and so on and so forth. Is there a way Acess can break the fields out? I want 1 field with CAN, 1 field with the year, 1 field with US, and 1 field with the number (4 different fields broken by the hyphen).
I can do text to column in excel before I import, but just wondering if it can be done in Access. Thanks in advance, guys...
Caliboi
I am attempting to create a report that breaks down a field of 'ClaimID' numbers into groups of x. In the sample report below x = 12 and the report will apportion the first 12 'ClaimID's to the first page and textboxes with extra large fonts will signify the start and end of 'ClaimID' numbers for that page. These sheets are used for sorting and pulling guides at our local Xmas project and x will vary depending on the size of the facility we're using.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport.gif
I believe I can attain my goal if I were able to create a query which broke down the field 'ClaimID' into multiple fields based on x. The sample below represents this breakdown creating multiple records with x, 4 and 5, amount of fields.
http://i35.photobucket.com/albums/d186/HoodRiverDude/SortReport2.gif
Does anyone know how I can create this query, or perhaps suggest another way to create this report using the existing 'ClaimID' data.
Thanks in advance,
Aldo
I have a report in which there is a subreport that prints towards the bottom of the page. I know that I can use grouping to have the subreport print on the next page if the entire subreport cannot print on the current page. However, I want to have the break occur not for the section as a whole but between records. Let me illustrate this.
All sorts of other stuff prints at the top. At the bottom, I have a subreport which today prints the following on the first page and breaks the last record in two printing the second line on the next page.
First Page of the Report:
__________________________________________________ _______________
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
__________________________________________________ _______________
Second Page of the Report:
__________________________________________________ _______________
Doctor's Location: XXXXXXXXXXXX
__________________________________________________ _______________
What I would like to do is the following:
First Page of the Report:
__________________________________________________ _______________
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
__________________________________________________ _______________
Second Page of the Report:
__________________________________________________ _______________
Doctor's Full Name: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Doctor's Location: XXXXXXXXXXXX
__________________________________________________ _______________
Is it possible to specify that if a complete record within a subreport cannot be printed on the same page to break to the next page without having the whole subreport break to the next page?
I have a date field on one of my forms that gets populated automatically when a new record is created. I also have a subform on the form that lists the tasks that still need to be accomplished this month.
Some problems have popped up as people got new computers with new versions of Access:
Access 2002(10.6501.6825) SP3 - No problems.
Access 2002(10.6771.6817) SP3 - The Date field isn't recognised on a new record (#Name?), but it's fine on the old records (displays date).
Access 2002(10.6771.6825) SP3 - The reminder subform is blank (white), as if it doesn't exist, but the date field works fine.
Any ideas why this might be/how to fix?
Thanks! This is really frustrating. If it didn't work for anyone, I would find a way to fix it, but because it works for some people, I don't know what to fix...
I am trying to figure out how to take a table of timestamps and activity duration and break it into intervals, where it groups the duration into the time spent in each 15 minute interval. I have included a sample of the data and output. Is there any way to pull this in Access (or SQL)?
Raw Data
EmployeeActivityTimeStampStartTimeStampEndTotalDuration
Doe,JohnSomeActivity12/16/13 9:06:02 AM12/16/13 9:57:14 AM0:51:12
Smith,JaneOtherActivity12/16/13 9:22:15 AM12/16/13 10:06:55 AM0:44:40
Query to break out the total duration time in to the 15 minute interval it fell into
EmployeeActivityIntervalIntervalDuration
Doe,JohnSomeActivity12/16/13 9:00:00 AM0:08:58
Doe,JohnSomeActivity12/16/13 9:15:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:30:00 AM0:15:00
Doe,JohnSomeActivity12/16/13 9:45:00 AM0:12:14
Smith,JaneOtherActivity12/16/13 9:15:00 AM0:07:45
Smith,JaneOtherActivity12/16/13 9:30:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 9:45:00 AM0:15:00
Smith,JaneOtherActivity12/16/13 10:00:00 AM0:06:55