Table Help (1 Viewer)

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
I'm not sure if anyone can help me but here it goes. I have values in one column in a table which is look up field and then have an amount field next to it. What I am trying to do is to get those two fields to be connected some how.

:banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:09
Joined
May 7, 2009
Messages
19,175
what do you mean?
this is wild guess.
create a query, the table you mentioned on post #1 and the lookup table.
relate the two tables using the lookup field.
edit the relationship. Using Join Type (2 - Left Join).
include the fields you want to show in the query on both tables.
 

MarkK

bit cruncher
Local time
Today, 08:09
Joined
Mar 17, 2004
Messages
8,178
If the two values are in the same row then they are already connected. What are you trying to do?
 

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
I am trying to get the values of column GL ACCT_# (this is a lookup field) to store the value entered in GL ACCT AMT.
 

Attachments

  • Capture.PNG
    Capture.PNG
    3.4 KB · Views: 82

plog

Banishment Pending
Local time
Today, 10:09
Joined
May 11, 2011
Messages
11,613
Stop whatever it is you are trying to do because you are heading down the wrong path.

When you start numerating field names (GL_ACCT_1, GL_ACCT_2, etc.) its time for a new table. Tables should not expand horizontally (with more columns) to accomodate data, but vertically (with more rows).

You need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and set up your tables properly. All the data you have should not be in 1 table, but at least 2.
 

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
I have the data spread out in multiple tables but I am having issues connecting the dots.
 

plog

Banishment Pending
Local time
Today, 10:09
Joined
May 11, 2011
Messages
11,613
The screenshot you posted it is not correct for a table. Was that a screenshot of a table? Have you in fact numerated your field names in a table?
 

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
Yes I made that mistake but i can easily go back and change the field names. My first time attempting something like this.
 

plog

Banishment Pending
Local time
Today, 10:09
Joined
May 11, 2011
Messages
11,613
Can you post a screenshot of your relationship tool? If you don't have that set up, can you post your database? If not that, then a screenshot of the table in design view so we can see all the fields in it.
 

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
Here it is, sorry I am trying to do an internal tracker for our department and there is just so much data that has to be considered.
 

Attachments

  • Capture-Table Design View.PNG
    Capture-Table Design View.PNG
    22 KB · Views: 77

plog

Banishment Pending
Local time
Today, 10:09
Joined
May 11, 2011
Messages
11,613
Yes, I was correct. You have numerated field names which means you need a new table. Read up on normalization.

You need a new table for all that GL_ACCT data. It would be structured like so:

GL_ACCT
InvoiceNumber, text, foreign key to [Invoice #].DATA_INVOICE_ENTRY
ACCT, short text, this will hold data currently going into GL_ACCT_# fields
AMT, currency, this will hold data currently going into GL_ACCT_#_AMT fields


That's it 3 fields for all that data. Then all those GL_ACCT fields come out of DATA_INVOICE_ENTRY. Instead of putting data into 6 different fields for GL_ACCT_# you would instead have 6 records in this new GL_ACCT table.
 

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
So what do I do when I have different GL ACCTs for the same invoice?
 

plog

Banishment Pending
Local time
Today, 10:09
Joined
May 11, 2011
Messages
11,613
You add them to this new table.
 

GenRonin

New member
Local time
Today, 11:09
Joined
Nov 21, 2017
Messages
8
How do i get the values from the GL table to populate on the invoice table?
 

plog

Banishment Pending
Local time
Today, 10:09
Joined
May 11, 2011
Messages
11,613
You can use APPEND queries, or manually do it.

Right now I suggest you work on the structure of your database, applying normalization to all of it. Don't move the data yet, just set up your tables properly. Then set up the Relationship Tool in Access and post a screenshot of it so we can verify its what you need.
 

Mark_

Longboard on the internet
Local time
Today, 08:09
Joined
Sep 12, 2017
Messages
2,111
Please note, when I see "GL Acct" and "GL Amt", I am assuming you are creating journal entries related to this invoice, correct?

To fill in your [GL ACCT], I'd use a combo box and have the source for that combo be your chart of accounts. You would want to limit it based on the nature of your entry (credit VS debit, valid accounts to post against).

Will you be posting to the accounts in addition to tracking journal entries for each PO?
 

Users who are viewing this thread

Top Bottom