Strange Form Request

[Sean SR]

Forms showing Multiple Records matching one field

Can anyone help me, I have a massive amount of Data that I need to audit, Month by Month.

The data is for Contracts, our company has with our clients. All Contracts have an annual renewal, so I audit the anniversary month for all contracts, to make sure they are correct, before invoices are created.

I have managed to get my data from excel in to access, as this is the second year, I am doing this. Excel was ok for year 1 but now I need to audit year 2, but refer to year 1, so I was advised that Access was the best way to go

I have managed to sort the data by query for the month I want to look at, and I have managed to create a tab form that shows what I want, however

Because the original data is exported as CSV out of MS dynamics it shows multiple lines for hardware and software, all based on the same contract number and site. Some Contracts could just have 1 line, some may have in excess of 20. But I need to see them all at the same time

I have created a mock database up based on one contract (changed all sensitive company data)

I have created 2 forms, (Test) is just getting the fields (that I have colour coded some sections to help me explain) (audit from 2022) is a tabular Form with data from the 2022 + 2021 tables

The Green section based on the contract number I would like to repeat for all lines. There could be up to 20+

The Blue section I would like, if possible, would have data in from 2021 on the first row only that needs to show in the (audit from 2022) 2021 Tab



And the Gold section would need to show again for all rows based on the contract number again in the (audit from 2022) 2021 Tab

I appreciate its not pretty but this is just a mock so I can play and learn and provide you a example

The original data has more than 17000 records in both 2021+2022

I have studied this week and advanced MS Access course, but it has given me the basics of what I need but I realise this request is not normal, if anyone can help I would really apricate it

Link to Dropbox location for example file: Example data -.accdb

[Gicu]

You should read more on database normalization. You will need to split the current table(s) in at least two or probably three tables:

tblClient would hold Client realted info such as ClientNumber, ClientName,Address,...with ClientID_PK as Primary Key (PK) - autonumber

tblContract would hold contract information such as ContractNumber, ContractStatus, ContractEffectiveDate, BillingFrequency with ContractID as primary key (autonumber); this table would also include the ClientID_FK (foreign key to relate to tblCLient)

tblContractItems would hold your individual line items again with a ContractItemID_PK autonaumber and ContractID_FK to link to the tblContract table.

Now your form will be setup as a mainform\subform(s) with the main form being bount to tblClient, the subform bound to tblContract and a second subform bound to thelContractItems (for the selected ContractID in the first subform).

Cheers,

[Sean SR]

Thanks you for you message

I am not sure I have the skills for that, but when I have read about database normalization, I see that many online pages recomend the Analyze table function. I think my original data is too big as it will not complete as the maxlocks per files are exceeded, even if I have increaed this it still fails

Could I do by using querries and linking the forms to querries?

[Gicu]

Yes you can use queries but it won't be much easier than creating the tables. Have a look at this updated file to see what I meant by the use of mainform\subform.
You should only have one set of tables for client info with a new Date field, not one for each year. You would then filter on the date field to get the data for the desired year.

Cheers,

[Sean SR]

Yes you can use queries but it won't be much easier than creating the tables. Have a look at this updated file to see what I meant by the use of mainform\subform.
You should only have one set of tables for client info with a new Date field, not one for each year. You would then filter on the date field to get the data for the desired year.

Cheers,

That is Brilliant Thank you very much, I have taken what you have done and replicated into my own database.
The Contract number was they Key not the customer number as a customer could have 1000's of contracts
but I think I have sorted that
I need to refer to audit bits in 2021 and update in 202 so created some extra querries
I have created the form and sub forms, so far the main form is working, and the subform Contract added
I will try and add in the rest and I need to add a navigation button to go to the next contract number
But Thank you very much I really appricate what you have done for me
I will try and share a screen shot when completed

[Sean SR]

Hi Gicu

Happy New Year
I really do thank you for your help. I have managed top get my form now and I have added 4 extra subfoms, all linked on the Contract number
Two little things I am struggling on is a command button to go to the next contract number, and a quick easy search based on the contract number, if you able to give me a little direction that would be awesome

[Micron]

You have navigation controls at the bottom of your form. Why not use them as long as you've elected to display them? Coding for navigation requires checks to ensure you're not at either end of the records and trying to move before or after the first/last record. Common topic, but makes no sense as long as you already have the built in controls? As for jumping to a particular record based on a contract number, perhaps research use of recordset bookmark as it's a common method.

https://docs.microsoft.com/en-us/off....form.bookmark

[Gicu]

The easiest way to build searching functionality is to use the built-in solution: add an unbound combo-box to the main form's header and when the wizard shows up select option number 3 (find a record...). Follow the steps and you have your easy to use searching method (it uses the bookmark method mentioned my Micron but I believe the default is an embedded macro instead of VBA, but you can convert it to VBA by opening the form in design view and clicking the "Convert All Macros" to VBA button on the ribbon).

Cheers,