User input items instead of permanently printed on reports

[jazsriel]

Nevermind, I will do as I generally do and figure it out myself.

 

[theDBguy]

Can't look at your db right now, but have you considered putting the contract number in a table and just look it up on your reports?

 

[XPS35]

It seems to me that if you want to know which contract number applies on a certain date, you should include a "date from" field in the tblContractnumber.

I did look at the database and was shocked at what I saw. Repeating groups are out of the question in a well-designed database. Your tables are completely full of them. So it is time for a thorough rebuild.

 

[plog]

Access is not Excel With Forms.

To expound on XPS35's comment, you've got bigger issues than the one you created this thread for. You need to start over and properly structure your tables. That process is called normalization:

Database normalization description - Microsoft 365 Apps

Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.

learn.microsoft.com

Give that link a read, google and work through few tutorials, then apply what you've learned to your database, fill out the Relationship Tool in Access and post a screenshot of it back here and we can help get you on the right track.

The biggest, most frequent mistake you've made is that tables should accomodate data vertically (with more rows) and not horizontally (with more fields). For example, instead of 9 fields for pallets (which I bet are not all used all the time) you should have a new table for that data and input 1 record for pallet. So if you had 5 pallets you'd input 5 rows of data into that field, not enter data into the first 5 pallet fields in tblRSHADaily and leave the other 4 blank. You've made this mistake quite often.

Similarly, actual data shouldn't be stored in field names. You've stuffed a ton of 'type' data into field names (Pallet, Semi, Perish, Sup, GR_Peri, etc.). All that data should not appear in field names, but in the table itself as values. If I ran a car dealership and tracked my inventory, I wouldn't have fields named after car types like this:

tblCars
ChevySparks, FordBroncos, ToyotaPriuses, HondaCRVs, EagleTalons, VWBugs...
7, 5, 1, 3, 9, 8, 4...

I would have my inventory like so:
tblCars
Make, Model, Inventory
Chevy, Spark, 7
Ford, Bronco, 5
Toyota, Prius, 1
Honda, CRV, 3
...

This let's me easily do aggregation on my data with standard query functions (SUM, COUNT, etc.), it is super expandable--if I later add Nissan Rogues to my inventory, I just need to add a record, not redesign the table by adding a new field in my table to accomodate it (which would also require fixing forms and reports).

That is how databases work, what you have done with yours is how spreadsheets work. Without knowing anything about your data other than your field names, I can easily see you need at least 2 more tables for this data. Again, read up on normalization and fix your database accordingly and post back here.

 

[jazsriel]

I already knew people were going to comment on the structure of the database. That is not the help I was looking for. I am simply looking for an answer to my question. Which restructuring my DB will not achieve that goal. I am not a database designer by any stretch of the imagination. I just need an idea on how to solve my current problem. But thank you for the comments, they will be referenced if I ever choose to restructure my DB.

 

[XPS35]

It seems to me that if you want to know which contract number applies on a certain date, you should include a "date from" field in the tblContractnumber.

This looks like an answer.

 

[LarryE]

It's pretty unlikely that any advisors in this forum is going to help you do something entirely wrong. I looked at your data as well and it's nonsense but having said that, DBGuy did give you your answer. Use The DLookUp function in a textbox control on your report to look up the contract value.

Anyone in this forum would be more than happy to assist you in creating your project properly from the beginning. Since you are probably going to have nothing but problems using ACCESS in this manner, I suspect you will need all the help you can get.

If, at some point, if you would like assistance in creating your ACCESS project properly, you can create a new post explaining:

1. What business the company engages in
2. What kinds of items or transactions or amounts you need to keep track of
3. What business workflow processes you are using currently

All these things affect how your project should be designed.

 

[jazsriel]

I'm sorry bit some things are privileged information, even posting things like that I am not really allowed to do

 

[LarryE]

I'm sorry bit some things are privileged information, even posting things like that I am not really allowed to do

No one is asking for sensitive information, but these designs always follow the business processes in place. For example, looking at the data and your form it seems that:

1. You have multiple delivery trucks
2. Each truck seems to have multiple pallets of perishable or non-perishable products
3. Each pallet may be located a a variety of multiple locations

If this is close, then at least we can give you a running start on a proper design.

 

[GPGeorge]

I started to respond to your comments by pointing out the incongruity in trying to use spreadsheet style tables in a relational database application. However, I realize that little short of complete failure is going to convince you to start over properly.

It's really an either - or choice. If you want to use spreadsheet style tables, put them in Excel and forgo the stresses of trying to use Access that way. If you want to use Access, put your data into relational tables. Otherwise you move from frustration to frustration to frustration. Access is designed to work a certain way and it's very, very hard to make it respond in other ways. You're trying to do that, and not being very successful at it.

It's hard to watch that; that is why so many people here would like to convince you to start afresh, following relational database design. That can come out in strongly worded admonitions. They are not hostile, though. They are trying to offer the best help they can. Take it or leave it. But do so gracefully either way.

 

[The_Doc_Man]

You have been told that your structure is going to give you fits later, so I won't focus on that. I will only say this: It is a "pay me now or pay me later" scenario given that structure. OK, no more comments on structure of the tables.

The problem I have with your question is that the title is ambiguous. I still don't understand from that short title what it is that you really want to do.

Can you give us at least a short paragraph on what you were thinking you wanted to do? (Taking into account that some of your data elements are proprietary or private in some way.)

 

[LarryE]

You have been told that your structure is going to give you fits later, so I won't focus on that. I will only say this: It is a "pay me now or pay me later" scenario given that structure. OK, no more comments on structure of the tables.

The problem I have with your question is that the title is ambiguous. I still don't understand from that short title what it is that you really want to do.

Can you give us at least a short paragraph on what you were thinking you wanted to do? (Taking into account that some of your data elements are proprietary or private in some way.)

DBGuy gave him the answer in #2. The OP already has the contract # in a table. All he has to do is DLookUp the value on a report textbox.

Is it just me or have we been getting more and more of these kinds of questions in here and when we advise them to construct the table and relationships properly, they refuse to cooperate. Maybe it's just climate change.

 

[GPGeorge]

DBGuy gave him the answer in #2. The OP already has the contract # in a table. All he has to do is DLookUp the value on a report textbox.

Is it just me or have we been getting more and more of these kinds of questions in here and when we advise them to construct the table and relationships properly, they refuse to cooperate. Maybe it's just climate change.

I see it on other forums, too.

I even wrote a tongue-in-cheek blog about the phenomenon.