First time using 2 tables for a Query....

[BenW]

as the topic says, i'm a beginner playing around w/ access and i tried to make a query that used 2 tables, but the problem is that when i try to view the query, (since i'm using fields from 2 diff tables may be why) none of the data shows up and all of the fields are blank.

i'm trying to create a query that runs off 2 tables in order to better organize my data. so what i'm trying to do is (with the invoice database i'm trying to create) get some fields from table1 to automaticallly roll over to table2 on the query because table1 and 2 use a different invoice numbering system but some of the values are identical for the y/n fields.

does anyone know how i can fix my query so that i can make it that it merges 2 tables together and still shows the data?

 

[BenW]

actually, no data shows up period. there aren't even blank rows to begin with in my fields. i tried using one table and the data showed up but when i added in another table all of the rows dissapeared again. does anyone know what's wrong?

 

[mattv10]

How are you connecting the tables?? Does your primary key link to a foriegn key?? For example, if you have an invoice database all invoice are going to have a unique id(which would be your primary key).. Therefore to link to another table you have to add the unique invoice ID to the proper row so the tables are linked properly.. I hope I explained that clearly, I'm not a pro at Access yet but my internship I'm currently working at has me working with access constantly!

Matt

 

[BenW]

i'm not quite sure what you mean by connecting the tables. do you mean using relationships?

i tried using relationships and i made a mess. i thought that if i connected all of the fields together then the info would show up but it didn't work so i have no idea how to delete the relationships and my relationships chart is a big mess with duplicate tables everywhere.

my invoice# is a primary key so i simply need to create a relationship between those 2 and the info should show up regardless of the fact that the numbers are completely different? i.e. table1 has different invoice numbers completely independent from table2.

(if i'm not supposed to use relationships to connect, then which feature do i use on access?)

 

[mattv10]

Ok first, to delete relationships click the relationship line and it should highlight bold. then just press delete.. You can also do this with the tables.. Click a table and then press delete..

Second, you cannot connect two tables with two different invoice numbers.. I'm alittle confused with that, you might have to explain to me what your trying to get the database to do. Sometimes thinking of a query that might be useful helps in designing table structure.. I think you might need to back up to pen and paper and design your table structure first(if you haven't already done so). This way your not confusing yourself with Access..

 

[BenW]

yea i already wrote out my design on paper. i've been working on this database for about a month now and i finished the central table. i wanted to create a realistic invoice system that my family could use since they are in the travel agency.

the reason i'm using 2 different invoice# is basically because each table represents the invoices of a different company and the companies are related in the sense that they have the same values for account payable and recieveables since it is the same customer. i wanted the query to use the different company's invoice number but copy the account P & account R values from the central table that i created so that values don't have to be retyped but when i tried doing this the rows didn't show up in the fields which was my problem. i hope this clarifys my issue.

oh and btw, thanks for telling me how to delete the relationships ^^

 

[mattv10]

Why don't you create a companies table.. You can have a companyID and one company can have many invoices(would be your relationship) Then in your invoice table you can have every possible invoice but use the companyID to verify which one it is.

How many tables do you have so far?? Could you kinda briefly explain how your tables are linking?? Sort of like I did above: You can have a companyID and one company can have many invoices(would be your relationship)..

Hope this helps

 

[BenW]

well i only had one table at the time and created my second (which was my second company). i think i'll revise the table to do the list of companies like you said w/ the company ID. i don't think my tables are linked at all because i'm not quite sure what it is. up until now i've been pretty dependant on online guides like drewslair and an office 2000 guide i borrowed from a friend.

so i guess now i would create a talbe listed with companies and assign each one a number then create a relationship with my main table and the company id table correct?

 

[mattv10]

This is correct, but remember to add a field in your invoice table and you can call it companyID... Then add the the matching companyID to all your invoices.

Only having two tables in your entire database is probably going to cause a lot of trouble.. Do you know anything about Database Normalization Basics?? If not try going here, its pretty good and uses an example database so you can understand what each rule says. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

[BenW]

small question, my table for company IDs has company name in one field and the ID in another. i made the ID primary on my Com ID table but invoice primary on the main table. is this going to affect the database later on or is it harmless?

edit:
is the ID number supposed to be replaced with the company name? because that isn't working for me.

i type in company name: Travel Int
and the company ID as: 1

i created a relationship between company ID (from table1) and company ID (from table2) and filled in the blanks with the number 1; however, when i opened the table to view it the 1's were not replaced with Travel Int and remained as 1. is this normal or is there something else wrong with what i'm doing? -.-

 

[mattv10]

Look your table should look something like this..

Every Table needs a primary key to make it unique so that is harmless. You just always need a Foreign key to make the relationship work.

CompanyTbl
--------------
CompanyID (PK)
Company
Address
(Whatever else you need)

InvoiceTbl
----------------
InvoiceID (PK)
(Whateverelse you need)
CompanyID (FK)

Now, click on the relationship button at the top of the toolbar.
1. right-click the relationship window and click Show Tables
2. Select the two tables
3. Drag the CompanyID (PK) field and drop it on the CompanyID (FK).
**You finished creating a relationship**

Next you want to create a query:
1. Select the Query button on the left side on the Access Main Window
2. Select create a query in design view.
3. Right click inside the query window and click show tables.
4. Add your two tables to the query window.
5. At the bottom where it says Table select your two tables and where it says field select your fields you want in your query..
**NOTE: you can select your tables as many times as you want to include as many fields as you want**

Hope this helps.

 

[BenW]

ok i finally got the data to show up! but it is still in numbers. i was wondering if that was normal. i see the company IDs but i always thought that the IDs were supposed to be replaced with numbers but it isn't a big issue for me. i was just wondering.

is there a way for me to pick out all of the entries of a single company? i.e. all invoices with company ID 2 get singled out ect....

this is really a HUGE help to me right now! i just wanted to thank you for how much you've been helping me so far. i don't know anyone that knows how to use access and i just recently found out that some guy from my school that said he was an "expert" in access/programming/ect.... was just running his mouth and didn't know anything when i asked him for help so i got stuck so these forums are my only source of help outside of the book that i have ><. thanks again in advance.

 

[mattv10]

Will use private messages from this point on. Sometimes forum admins get mad when you continually post numerous threads.. Atleast that is the experience I have had.. Are you firmilar with using Private messages??? If so, send me one and we will continue from there..

I'm alittle confused about what you said first but to find all the invoices for a certain company you would use the keyword WHERE.. However, I'm not sure how to implement that in Access, I'll have to look it up real quick and get back to you.. In the mean time send me a private msg