I currently have 3 tables that contain several of the same fields, but also several different fields. I have the relationships linked to the main "item". I have a report that needs to list several different items under one heading, but the only results I am getting is "Name?". I have the query, the report, and all three tables already setup but can't get it to work. Can someone please help?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Multiple Tables used to populate information on one Report
- Thread starter 4304
- Start date
- Thread starter
- #3
Several items under one heading" - I have a field named Item Numbers - there are 19 different Item Numbers and I need them all to show up on my report.
The query is set up as follows:
Item # WO # PO # Vendor MR Title Inv # etc...
Reca tbl Inv log Inv log Inv log PO log Reca tbl
Ascendi
[Forms]![Inv Log]![PO #]
This example was shortened to save space, but the PO # is the field that all tables have in common.
Hopefully this helps, cause I really need you. Thanks again!
The query is set up as follows:
Item # WO # PO # Vendor MR Title Inv # etc...
Reca tbl Inv log Inv log Inv log PO log Reca tbl
Ascendi
[Forms]![Inv Log]![PO #]
This example was shortened to save space, but the PO # is the field that all tables have in common.
Hopefully this helps, cause I really need you. Thanks again!
1) OK, can you list the fields from each table that you need in the query, including duplicates?
2) When you say there are 19 item numbers, are there 19 records with a differen item number, or 19 fields for item numbers?
3) When you say "under the same heading", do you mean you want each item number as a heading with details within each, or do you mean there are different item number fields and you want the data from them in one column of the report?
Have fun!
)
Alex Middleton
2) When you say there are 19 item numbers, are there 19 records with a differen item number, or 19 fields for item numbers?
3) When you say "under the same heading", do you mean you want each item number as a heading with details within each, or do you mean there are different item number fields and you want the data from them in one column of the report?
Have fun!
)Alex Middleton
- Thread starter
- #5
1) OK, can you list the fields from each table that you need in the query, including duplicates?
Item Number
Client WO Number
Client PO Number
Vendor
MR Title
Invoice Number
Date
Quantity
Taxes
Freight
Invoice Amount
PO Total
There aren't any duplicate fields.
2) When you say there are 19 item numbers, are there 19 records with a different item number, or 19 fields for item numbers?
There are 19 different item numbers. For example, item number 1, which is a valve, was invoiced on invoice #668705 and #668706. They are entered as item number 1 on two different records. I would need it to list both records.
3) When you say "under the same heading", do you mean you want each item number as a heading with details within each, or do you mean there are different item number fields and you want the data from them in one column of the report?
I want it to be like this (short form example only):
Item Number Invoice Number Date etc...
1 668705 1/26/05
1 668706 1/31/05
2 668707 2/10/05
2 668705 2/12/05
2 668706 3/1/05
3 668707 2/10/05
Thanks again!
Item Number
Client WO Number
Client PO Number
Vendor
MR Title
Invoice Number
Date
Quantity
Taxes
Freight
Invoice Amount
PO Total
There aren't any duplicate fields.
2) When you say there are 19 item numbers, are there 19 records with a different item number, or 19 fields for item numbers?
There are 19 different item numbers. For example, item number 1, which is a valve, was invoiced on invoice #668705 and #668706. They are entered as item number 1 on two different records. I would need it to list both records.
3) When you say "under the same heading", do you mean you want each item number as a heading with details within each, or do you mean there are different item number fields and you want the data from them in one column of the report?
I want it to be like this (short form example only):
Item Number Invoice Number Date etc...
1 668705 1/26/05
1 668706 1/31/05
2 668707 2/10/05
2 668705 2/12/05
2 668706 3/1/05
3 668707 2/10/05
Thanks again!
OK, so what are the three tables and how are they joined? I really need to know which table each of the listed fields comes from and how the three tables relate to each other. I am assuming there is a table for items, one for invoices and one for something else, say Vendors. Let me knwo, and perhaps we can lick this.
Have fun!)
Alex Middleton
Have fun!
)Alex Middleton
OK, so what are the three tables and how are they joined? I really need to know which table each of the listed fields comes from and how the three tables relate to each other. I am assuming there is a table for items, one for invoices and one for something else, say Vendors. Let me know, and perhaps we can lick this.
Have fun!)
Alex Middleton
Have fun!
)Alex Middleton
- Thread starter
- #8
Okay,
Item Number - Invoice Recap Table
Client WO Number - Invoice Log
Client PO Number - Invoice Log
Vendor - Invoice Log
MR Title - PO Log
Invoice Number - Invoice Recap Table
Date - Invoice Recap Table
Quantity - Invoice Recap Table
Taxes - Invoice Log
Freight - Invoice Log
Invoice Amount - Invoice Log
PO Total - PO Log
We have the PO Log, the Invoice Log, and the Invoice Recap Table. They are all linked by the PO Number which doesn't change.
The PO Log has all the purchase order information, i.e. PO #, WO #, etc. The Invoice Log is where I log all my invoices, it contains the inv. #, inv amount, vendor, etc. The Recap Table is where the line items, quantity, etc... are logged.
The form I need this information to go to is the Invoice Recap Form. It's something like this:
CWO#: 047311 PO#: W-20051549 Vendor: Red Man Pipe
Item Number Invoice Number Date etc...
1 668705 1/26/05
1 668706 1/31/05
2 668707 2/10/05
2 668705 1/26/05
2 668706 1/31/05
3 668707 2/10/05
Thanks!
Item Number - Invoice Recap Table
Client WO Number - Invoice Log
Client PO Number - Invoice Log
Vendor - Invoice Log
MR Title - PO Log
Invoice Number - Invoice Recap Table
Date - Invoice Recap Table
Quantity - Invoice Recap Table
Taxes - Invoice Log
Freight - Invoice Log
Invoice Amount - Invoice Log
PO Total - PO Log
We have the PO Log, the Invoice Log, and the Invoice Recap Table. They are all linked by the PO Number which doesn't change.
The PO Log has all the purchase order information, i.e. PO #, WO #, etc. The Invoice Log is where I log all my invoices, it contains the inv. #, inv amount, vendor, etc. The Recap Table is where the line items, quantity, etc... are logged.
The form I need this information to go to is the Invoice Recap Form. It's something like this:
CWO#: 047311 PO#: W-20051549 Vendor: Red Man Pipe
Item Number Invoice Number Date etc...
1 668705 1/26/05
1 668706 1/31/05
2 668707 2/10/05
2 668705 1/26/05
2 668706 1/31/05
3 668707 2/10/05
Thanks!
OK, I see what you need to appear in the report, but I am still confused about the arrangement of your tables. What are the Primary/Foreign Keys in each table and what are the relationships (One-to-Many etc). This is required so that the query can be put together from which the report will be built.
Have fun!)
Alex Middleton
Have fun!
)Alex Middleton
- Thread starter
- #10
The primary key is the PO # in each table. I have relationships by PO # with the PO log and the Invoice Log - indeterminate (whatever that means), by PO # with the PO Log and the Invoice Recap Table - indeterminate, by PO # with the Invoice Log and the Invoice Recap Table.
I have the query setup and everything pops up correctly on the datasheet view. I just can't get it to populate my fields on my report.
Please don't give up on me, I hope I am helping. Thanks again!
I have the query setup and everything pops up correctly on the datasheet view. I just can't get it to populate my fields on my report.
Please don't give up on me, I hope I am helping. Thanks again!
OK, I am still a little confused about the table arrangement (why would PO# be the primary key in an invoice table, for instance) but I am assuming that you want the report grouped by PO# (I know you have other fields in the heading but they complicate things so I will stick to the PO# for now).
So, you should have the report sorted by PO# (in "Grouping and Sorting"), and a Group Header for that field. Place the PO# text box in the header. Then place the text boxes for Item #, Invoice #, Date etc (i.e. the fields you want to appear under each PO) in the details section.
This may not be exactly what you want but it is a start. See how it goes then advise if it is correct, not correct but getting there, or totally incorrect.
Have fun!)
Alex Middleton
So, you should have the report sorted by PO# (in "Grouping and Sorting"), and a Group Header for that field. Place the PO# text box in the header. Then place the text boxes for Item #, Invoice #, Date etc (i.e. the fields you want to appear under each PO) in the details section.
This may not be exactly what you want but it is a start. See how it goes then advise if it is correct, not correct but getting there, or totally incorrect.
Have fun!
)Alex Middleton
- Thread starter
- #12
You got it so far, but I am just a tad bit ahead of you, I already have everything setup. I have the report made and the text boxes all laid out with the correct "formulas" already in them.
It's just for some reason, instead of the information I need, I am getting #Name?.
It's just for some reason, instead of the information I need, I am getting #Name?.
If you are getting #Name in the fields, then there is a typo or an error in the reference for that field - it can't find the field in the query due to the typo or it can't calculate the value for the same reason.
Do you have an example field and the reference that is in it that is causing a problem?
Have fun!)
Alex Middleton
Do you have an example field and the reference that is in it that is causing a problem?
Have fun!
)Alex Middleton
- Thread starter
- #14
Here is what I have in the item number field:
=[Forms]![Invoice Recap Form]![Item Number] = #Name?
Here is what I have in the Vendor field:
=Forms![Invoice Log]!Vendor = Red Man Pipe & Supply
I'm not sure what I am doing wrong.
I also just noticed that it is only giving me information from my invoice log, for example, i have the WO#, the PO#, the Vendor, taxes, freight, and invoice amount all coming from the invoice log. those fields are populating perfectly, however, it's like the report is not reading the information from the other tables, but query is working perfectly.
Thanks for your patience.
That is totally confusing for me to read
=[Forms]![Invoice Recap Form]![Item Number] = #Name?
Here is what I have in the Vendor field:
=Forms![Invoice Log]!Vendor = Red Man Pipe & Supply
I'm not sure what I am doing wrong.
I also just noticed that it is only giving me information from my invoice log, for example, i have the WO#, the PO#, the Vendor, taxes, freight, and invoice amount all coming from the invoice log. those fields are populating perfectly, however, it's like the report is not reading the information from the other tables, but query is working perfectly.
Thanks for your patience.
That is totally confusing for me to read
- Status
Similar threads
- Locked
- Question
- Locked
- Question