Tables not communicating (1 Viewer)

The purpose of this database is to collect information from contractor payments for members hourly work and track it for their future benefit tracking

I am attaching a sample of my database which only consists of 3 tables currently. I need these tables to talk through an entry form I will create once I get my relationships correct. List of what I need these tables to do is below:

contpay is getting its data from an entry

the entry form will have all the fields from this table other than the ID field
when the Acct# is entered it should match it to the SSN field in the MEMlist table and show it on the entry form to confirm it is the correct individual

the entry form will also obtain the contno which I would like for it to go to the cont table and get the contractor name and display it on the entry form (TRULY driving me CRAZY!!!!) Really have NO CLUE how to make this happen captain!!!

Once I can get these tables to talk I would like to create a dashboard, which I believer I can do, so the user will never access the raw data. it will also develop reports from the contpay table which I also believe I can make happen!

ANY help is greatly appreciated!!! I believe this will be really easy for yall go getters!! Thanks in advance for any and ALL help!! Yall are amazing!!

:banghead::banghead::banghead:


UNSURE HOW TO ATTACH THE FILE

Hi. I don't see any attachment. To attach your file, try zipping it up first. Also, it might be better to describe the purpose of the database rather than the how you want it to work. If we knew what it's for, we might have other ideas on how to make it work for you.

I do not see anywhere for attaching a file

Mother_weaver said:

I do not see anywhere for attaching a file

Click to expand...

It's towards the bottom as you post your reply. Click the button labeled "Manage Attachments."

Yeah!!! Attached!!!

Mother_weaver said:

Yeah!!! Attached!!!

Click to expand...

Good job. How about the purpose/description for your db?

it will also develop reports from the contpay table which I also believe I can make happen!

Click to expand...

That is the second step, not making forms. No use making amazingly beautiful input forms that just throw data down a hole from which you can't retreive it. Reports and the queries that support them help you validate your table structure. So, focus on your tables, then make the reports, then worry about input forms.

As for your tables, you've made a common mistake- Numerated field names. When you feel the need to prefix/suffix fields with numbers it means that data requires its own table with drastically less fields. You have done this with all those PYPDX fields in TBLCONTPAY.

Instead you should have a new table (e.g. TBLPYPD) that has a 1-many relationship to TBLCONTPAY. It would be structred like so:

TBLPYPD
PYPD_ID, autonumber, primary key
ID_CONTPAY, number, foreign key to TBLCONTPAY.ID
PYPD, number, this will hold the value each of those 5 fields now holds


That's it. So instead of 5 fields in TBLCONTPAY for this data, you would accomodate the data with 5 records in the new table. Additionally, if the number you've suffixed to the field name in TBLCONTPAY is important you would have an additional field (PYPD_NUM) to hold it.

Lastly, only use alphanumeric characters and underscores in field/table names. That means no # in field names (Mem#, Store#-->MemNumber, StoreNumber).

The PYPD1 has to be manually entered for the 4-5 weeks of the month. This tracks member hours and totals. Has be apart of the entry.

My biggest problem is the contractor name vs contractor number. I can't get a field to produce the contractor name. I want the number to be entered in the form and the name to show up in another portion of the form. So im guessing I need to write a query for this?

The PYPD1 has to be manually entered for the 4-5 weeks of the month. This tracks member hours and totals. Has be apart of the entry.

Click to expand...

Was that an explanation for why you are not normalizing your data properly? If so, its a poor one and you should properly structure your tables.

I want the number to be entered in the form and the name to show up in another portion of the form.

Click to expand...

Again, forms are the last portion of this. I know they are the sexy part everyone wants to jump straight into, but they are the last part of building a database. With that said, its simple to make a combo box display one field and use another:

https://www.techonthenet.com/access/comboboxes/bind_index.php

Again, forms are the last portion of this. I know they are the sexy part everyone wants to jump straight into, but they are the last part of building a database

Click to expand...

FWIW, my philosophy is different.
Tables first.
Queries second
- validate tables and relationships (if created)
- base forms/reports on queries, not tables, thus queries before reports
- a form based on a query that isn't editable and needs to be is useless
Forms third
- getting data out is putting the cart before the horse if it isn't already in
Reports fourth

Not to say any other method is wrong, just that it seems to me that this isn't chiseled in stone.